Mit der SVERWEIS-Funktion holen Sie einen Wert aus einer anderen Tabelle, ohne ihn abzutippen. Sie geben eine Artikelnummer vor, und Excel liefert die passende Bezeichnung und den Preis. Das ist immer dann hilfreich, wenn Angaben in zwei Listen verteilt liegen und Sie beide zusammenführen möchten.
In diesem Beitrag lösen Sie vier Aufgaben aus dem Büroalltag eines Händlers für Bürobedarf: Preise in eine Bestellliste übernehmen, offene Rechnungen mit den Zahlungseingängen abgleichen, mit zwei Suchkriterien arbeiten und einen Rabatt aus einer Umsatzstaffel bestimmen. Am Ende finden Sie die häufigsten Ursachen für die Fehlermeldung #NV und eine Übungsdatei mit allen Tabellen.
Was die SVERWEIS-Funktion macht
SVERWEIS steht für senkrechter Verweis. Die Funktion sucht einen Wert in der ersten Spalte einer Tabelle, geht in der gefundenen Zeile nach rechts und gibt den Wert aus der Spalte zurück, die Sie angeben. Gesucht wird also von oben nach unten, ausgegeben wird nach rechts.
Daraus ergeben sich zwei Bedingungen, die Sie kennen sollten. Der Suchbegriff muss in der ersten Spalte des Bereichs stehen, den Sie durchsuchen lassen. Und die Spalte mit dem Ergebnis muss rechts davon liegen. Nach links kann SVERWEIS nicht suchen.
Die Funktion hat vier Argumente:
| Argument | Bedeutung |
| Suchkriterium | Der Wert, nach dem gesucht wird. Meist ein Zellbezug wie C4, möglich sind auch ein fester Text in Anführungszeichen oder eine Zahl. |
| Matrix | Der Bereich, der durchsucht wird. Die erste Spalte dieses Bereichs enthält die Suchbegriffe. Der Bereich muss weit genug nach rechts reichen, damit die Ergebnisspalte enthalten ist. |
| Spaltenindex | Die Nummer der Spalte innerhalb der Matrix, aus der das Ergebnis kommt. Gezählt wird ab der ersten Spalte der Matrix, nicht ab Spalte A des Arbeitsblatts. |
| Bereich_Verweis | FALSCH sucht nach einer genauen Übereinstimmung. WAHR sucht den nächstkleineren Wert und setzt eine aufsteigend sortierte erste Spalte voraus. |
Das vierte Argument ist der häufigste Stolperstein. Lassen Sie es weg, rechnet Excel mit WAHR und liefert bei unsortierten Daten stillschweigend falsche Werte. Schreiben Sie deshalb in allen Fällen, in denen Sie einen genauen Treffer brauchen, ausdrücklich FALSCH hinein.
Aufgabe 1: Preise in eine Bestellliste übernehmen
Auf dem Blatt Bestellungen stehen Datum, Bestellnummer, Artikelnummer und Menge. Bezeichnung, Einzelpreis und Gesamtpreis fehlen. Diese Angaben liegen auf einem zweiten Blatt in der Preisliste.
Die Artikelnummer in Spalte C ist die Verbindung zwischen beiden Tabellen. Nach ihr sucht SVERWEIS in der Preisliste.
Die Preisliste als Nachschlagetabelle
Wechseln Sie auf das Blatt Preisliste. Die Artikelnummern stehen dort in Spalte A, also in der ersten Spalte. Damit ist die Grundbedingung für SVERWEIS erfüllt.
Der Bereich A4:D13 ist die Matrix. Er beginnt bei den Artikelnummern und reicht bis zur Spalte mit dem Einzelpreis. Die Kopfzeile in Zeile 3 gehört nicht dazu, sonst könnte Excel die Überschrift als Treffer ausgeben.
Den Spaltenindex bestimmen
Zählen Sie die Spalten innerhalb der Matrix, beginnend bei der Suchspalte mit der Nummer 1. In diesem Beispiel ist die Artikelnummer Spalte 1, die Bezeichnung Spalte 2, die Kategorie Spalte 3 und der Einzelpreis Spalte 4.
Zählen Sie nie die Spaltenbuchstaben des Arbeitsblatts. Beginnt die Matrix bei Spalte C, ist C die Spalte 1. Genau hier entstehen die meisten falschen Ergebnisse.
Die Formel eingeben
Gehen Sie zurück auf das Blatt Bestellungen und klicken Sie in die Zelle E4. Geben Sie die Formel ein:
=SVERWEIS($C4;Preisliste!$A$4:$D$13;2;FALSCH)Bestätigen Sie mit Enter. In E4 erscheint die Bezeichnung des Artikels BB-3001, also Ordner A4 breit, schwarz.
So sind die vier Argumente in dieser Formel belegt:
=SVERWEIS($C4;Preisliste!$A$4:$D$13;2;FALSCH)- $C4 ist das Suchkriterium, also die Artikelnummer dieser Zeile.
- Preisliste!$A$4:$D$13 ist die Matrix auf dem anderen Blatt. Das Ausrufezeichen trennt den Blattnamen vom Bereich.
- 2 ist der Spaltenindex, hier die Bezeichnung.
- FALSCH verlangt eine genaue Übereinstimmung der Artikelnummer.
Sie müssen den Bereich nicht abtippen. Setzen Sie den Cursor an die Stelle des zweiten Arguments, wechseln Sie auf das Blatt Preisliste und markieren Sie dort A4:D13 mit der Maus. Excel trägt den Bezug samt Blattnamen selbst ein.
Die Formel nach unten kopieren
Ergänzen Sie in F4 den Einzelpreis und in G4 den Gesamtpreis:
=SVERWEIS($C4;Preisliste!$A$4:$D$13;4;FALSCH)
=D4*F4Markieren Sie anschließend E4:G4 und ziehen Sie das kleine Quadrat an der rechten unteren Ecke der Markierung bis Zeile 11 nach unten. Excel füllt die restlichen Zeilen aus.
Warum die Dollarzeichen nötig sind
Die Schreibweise $A$4:$D$13 nennt sich absoluter Bezug. Sie sorgt dafür, dass der Suchbereich beim Kopieren an seiner Stelle bleibt. Ohne Dollarzeichen wandert der Bereich mit jeder Zeile eine Zeile nach unten. In Zeile 9 durchsucht Excel dann nur noch Preisliste!A9:D18, und die ersten Artikel fehlen darin.
Das Ergebnis ist tückisch, weil ein Teil der Zeilen weiterhin stimmt. Setzen Sie die Dollarzeichen deshalb sofort. Markieren Sie den Bereich in der Bearbeitungsleiste und drücken Sie F4, dann ergänzt Excel sie für Sie. Mehr dazu lesen Sie unter relative und absolute Zellbezüge.
Beim Suchkriterium $C4 ist nur der Spaltenbuchstabe festgesetzt. Die Zeilennummer bleibt beweglich, damit jede Zeile ihre eigene Artikelnummer nachschlägt.
Aufgabe 2: Offene Rechnungen mit den Zahlungseingängen abgleichen
Auf dem Blatt Zahlungen stehen links sieben Rechnungen, rechts die vier Zahlungseingänge aus dem Kontoauszug. Gesucht ist die Antwort auf eine einfache Frage: Welche Rechnung ist noch offen?
Geben Sie in D4 diese Formel ein und kopieren Sie sie bis Zeile 10 nach unten:
=SVERWEIS(A4;$F$4:$G$7;2;FALSCH)
Drei Zeilen zeigen #NV. Das ist hier keine Panne, sondern das Ergebnis: Zu diesen Rechnungen gibt es keinen Zahlungseingang. #NV steht für nicht verfügbar. Bei SVERWEIS heißt das, dass der Suchbegriff in der ersten Spalte der Matrix nicht vorkommt.
Statt #NV einen lesbaren Hinweis ausgeben
Für eine Liste, die weitergereicht wird, ist #NV unschön. Legen Sie WENNFEHLER um die Formel herum:
=WENNFEHLER(SVERWEIS(A4;$F$4:$G$7;2;FALSCH);"offen")
WENNFEHLER gibt das Ergebnis der Formel aus, solange alles glatt läuft. Tritt ein Fehler auf, erscheint stattdessen der zweite Wert, hier das Wort offen.
Ein Hinweis dazu: WENNFEHLER unterdrückt jeden Fehler, auch einen falschen Spaltenindex. Setzen Sie es erst ein, wenn die Formel nachweislich richtig rechnet. Sonst verstecken Sie damit einen Fehler, statt ihn zu sehen.
Wenn Sie ausschließlich #NV abfangen möchten und alle anderen Fehler sichtbar bleiben sollen, verwenden Sie stattdessen =WENNNV(SVERWEIS(A4;$F$4:$G$7;2;FALSCH);"offen"). Diese Funktion gibt es ab Excel 2013.
Wenn Sie die offenen Posten zusätzlich farbig hervorheben möchten, kombinieren Sie das Ergebnis mit einer bedingten Formatierung. Weitere Wege für diesen Abgleich stehen unter zwei Excel-Tabellen vergleichen, und die Funktion selbst ist unter WENNFEHLER ausführlich beschrieben.
Aufgabe 3: Mit zwei Suchkriterien arbeiten
Viele Preise hängen nicht nur am Artikel, sondern auch am Kunden. Auf dem Blatt Kundenpreise hat jeder Artikel drei Preise, je nach Kundengruppe. Die Artikelnummer allein reicht als Suchbegriff deshalb nicht aus, denn sie kommt dreimal vor. Erst Artikelnummer und Kundengruppe zusammen ergeben einen eindeutigen Treffer.
SVERWEIS kann nur nach einem Wert suchen. Die Lösung besteht darin, aus zwei Angaben eine zu machen: eine Hilfsspalte.
Die Hilfsspalte anlegen
Spalte A ist dafür frei geblieben, denn die Hilfsspalte muss links stehen. SVERWEIS sucht immer in der ersten Spalte der Matrix. Geben Sie in A4 ein und kopieren Sie die Formel bis Zeile 15 nach unten:
=B4&C4
Das kaufmännische Und verbindet zwei Zellinhalte zu einem Text. Aus BB-3001 und Partner wird BB-3001Partner. Dieser zusammengesetzte Schlüssel kommt in der Tabelle nur einmal vor.
Mit dem zusammengesetzten Schlüssel suchen
Im Angebot rechts stehen Artikelnummer und Kundengruppe in getrennten Spalten. Verbinden Sie beide im Suchkriterium auf dieselbe Weise. Geben Sie in I4 ein:
=SVERWEIS(F4&G4;$A$4:$D$15;4;FALSCH)
Die Matrix beginnt bei der Hilfsspalte A und reicht bis zur Preisspalte D. Der Spaltenindex 4 zählt wieder ab der Matrix: Schlüssel, Artikelnummer, Kundengruppe, Preis.
Drei Punkte machen in der Praxis Ärger. Erstens muss die Reihenfolge in beiden Verkettungen gleich sein. B4&C4 auf der einen Seite verlangt F4&G4 auf der anderen. Zweitens stören Leerzeichen: Steht in einer Zelle Partner mit einem Leerzeichen am Ende, entsteht ein anderer Schlüssel und die Suche schlägt fehl.
Drei weitere Wege ohne Hilfsspalte, darunter XVERWEIS und SUMMEWENNS, stehen unter SVERWEIS mit zwei Suchkriterien.
Drittens können zwei Zahlen denselben Schlüssel ergeben. Aus 12 und 345 wird 12345, aus 123 und 45 ebenfalls. In diesem Beispiel kann das nicht passieren, weil die Artikelnummern Buchstaben und einen Bindestrich enthalten. Bestehen Ihre Spalten aus reinen Zahlen, setzen Sie ein Trennzeichen dazwischen, das in den Daten nicht vorkommt: =B4&"|"&C4. Dann muss auch das Suchkriterium F4&"|"&G4 lauten.
Aufgabe 4: Rabatt aus einer Umsatzstaffel bestimmen
Bisher sollte SVERWEIS immer genau treffen. Jetzt kommt der Fall, für den das vierte Argument WAHR gedacht ist. Auf dem Blatt Rabatte steht links eine Staffel: ab 1.000 Euro Jahresumsatz gibt es 2 Prozent, ab 2.500 Euro 4 Prozent und so weiter. Rechts stehen die Jahresumsätze der Kunden.
Ein Umsatz von 8.420,50 Euro steht in der Staffel nicht. Mit FALSCH käme hier #NV heraus. Gesucht ist der nächstkleinere Wert, also die Stufe 5.000 Euro. Genau das macht WAHR.
Geben Sie in G4 und H4 ein und kopieren Sie beide Formeln bis Zeile 9 nach unten:
=SVERWEIS(F4;$A$4:$B$8;2;WAHR)
=SVERWEIS(F4;$A$4:$C$8;3;WAHR)
Jeder Kunde bekommt die Stufe, die zu seinem Umsatz gehört. 640 Euro ergeben 0 Prozent, 12.750,80 Euro ergeben 8 Prozent.
Für diese Variante gelten zwei Bedingungen. Die erste Spalte der Staffel muss aufsteigend sortiert sein, sonst liefert Excel falsche Werte ohne Fehlermeldung. Und die Staffel braucht eine unterste Stufe, hier die Zeile mit 0,00 Euro. Fehlt sie, erhalten alle Kunden unterhalb der ersten Grenze ein #NV. Wie Sie Listen sortieren, steht unter Sortieren und Filtern von Daten.
SVERWEIS meldet #NV: die häufigsten Ursachen
Die Fehlermeldung #NV sagt nur, dass der Suchbegriff nicht gefunden wurde. Warum nicht, verrät sie nicht. Diese Ursachen decken die meisten Fälle ab, ausführlich mit Prüfschritten stehen sie unter SVERWEIS funktioniert nicht:
| Was Sie sehen | Ursache | Lösung |
Der Wert ist sichtbar vorhanden, trotzdem #NV | Eine Seite enthält eine Zahl, die andere denselben Wert als Text | Beide Seiten auf denselben Typ bringen. Steht der Suchbegriff als Text vor, hilft WERT(), im umgekehrten Fall &"" |
#NV nur in einigen Zeilen | Der Suchbereich ist beim Kopieren verrutscht | Dollarzeichen setzen: $A$4:$D$13 |
Alle Zeilen zeigen #NV | Der Suchbegriff steht nicht in der ersten Spalte der Matrix | Matrix so wählen, dass sie bei der Suchspalte beginnt |
#NV bei scheinbar gleichem Text | Leerzeichen am Anfang oder Ende, etwa aus einem Export | GLÄTTEN() verwenden oder die Quelldaten bereinigen |
| Falscher Wert statt Fehlermeldung | Das vierte Argument fehlt, Excel rechnet mit WAHR | FALSCH ergänzen |
#BEZUG! statt eines Werts | Der Spaltenindex ist größer als die Matrix breit ist | Spalten in der Matrix zählen und den Index anpassen |
Der häufigste Fall: Zahl gegen Text
Auf dem Blatt Fehlersuche sehen Sie den Klassiker. Links steht der Kundenstamm mit Kundennummern als Zahl, rechts ein Export aus der Warenwirtschaft. Dort sind dieselben Nummern als Text gespeichert.
Zwei Hinweise deuten darauf hin. Die Zahlen links sind rechtsbündig, die Werte rechts linksbündig. Und in der linken oberen Ecke der Exportzellen sitzt ein grünes Dreieck. Sicher ist die Prüfung mit =ISTZAHL(E4): Kommt FALSCH heraus, führt Excel den Inhalt als Text.
Für eine schnelle Lösung wandeln Sie das Suchkriterium in der Formel um:
=SVERWEIS(WERT(E4);$A$4:$C$9;3;FALSCH)
Umgekehrt funktioniert es genauso: Steht der Suchbegriff als Zahl vor und die Liste enthält Text, hängen Sie mit E4&"" eine leere Zeichenfolge an. Sauberer ist es, die Spalte dauerhaft umzuwandeln. Markieren Sie die Zellen, klicken Sie auf das Warnsymbol und wählen Sie In Zahl umwandeln.
Was SVERWEIS nicht kann
Drei Grenzen sollten Sie kennen, bevor Sie lange nach einem Fehler suchen, den es nicht gibt.
- Nach links suchen. Steht die gesuchte Angabe links vom Suchbegriff, hilft SVERWEIS nicht weiter. Dafür gibt es die Kombination aus INDEX und VERGLEICH oder den XVERWEIS.
- Mehrere Treffer ausgeben. SVERWEIS liefert immer nur den ersten Treffer von oben. Wie Sie alle passenden Zeilen ausgeben, zeigt ein eigener Beitrag.
- Spalten einfügen überstehen. Der Spaltenindex ist eine feste Zahl. Fügen Sie innerhalb der Matrix eine Spalte ein, zeigt die Formel auf die falsche Spalte, ohne dass eine Fehlermeldung erscheint.
SVERWEIS oder XVERWEIS
XVERWEIS ist der Nachfolger und nimmt zwei dieser drei Grenzen weg: Er sucht in jede Richtung und übersteht das Einfügen von Spalten, weil er keinen Spaltenindex braucht. Beim dritten Punkt hilft er nicht, auch XVERWEIS gibt nur den ersten Treffer zurück. Dafür lässt er sich für den Fall ohne Treffer mit einem eigenen Text versehen. In Excel 2016 und Excel 2019 gibt es ihn nicht.
| SVERWEIS | XVERWEIS | |
| Verfügbar in | allen Excel-Versionen | Microsoft 365, Excel 2021 und Excel 2024 |
| Suchrichtung | nur nach rechts | in jede Richtung |
| Ergebnisspalte | als Nummer | als Bereich |
| Verhalten ohne Treffer | #NV | eigener Text möglich |
| Standard bei fehlendem letzten Argument | ungefähre Suche | genaue Suche |
Ein Grund spricht weiterhin für SVERWEIS: Die Funktion läuft in jeder Excel-Version. Geben Sie eine Datei an Kolleginnen und Kollegen mit älteren Versionen weiter, erscheint bei XVERWEIS ein #NAME?-Fehler.
SVERWEIS auf Englisch
In einer englischen Excel-Version heißt die Funktion VLOOKUP. Die Argumente lauten dort lookup_value, table_array, col_index_num und range_lookup, statt WAHR und FALSCH schreiben Sie TRUE und FALSE.
Häufig steht dort zwischen den Argumenten ein Komma statt eines Semikolons. Das hängt allerdings nicht an der Sprache, sondern am Listentrennzeichen der Regionseinstellungen des Rechners.
Eine Datei, die Sie in einer deutschen Version erstellen, lässt sich trotzdem in einer englischen öffnen. Excel speichert die Funktionsnamen intern in Englisch und zeigt sie in der Sprache an, die eingestellt ist. Die Formel muss lediglich in der Zielversion vorhanden sein.
Übungsdatei zum Herunterladen
Alle Tabellen dieses Beitrags stecken in einer Arbeitsmappe. Die Ergebnisspalten sind gelb hinterlegt und leer, damit Sie die Formeln selbst eingeben können.
Was in der Übungsdatei steckt
- Bestellungen und Preisliste für die Grundlagen
- Zahlungen für den Abgleich zweier Listen
- Kundenpreise für die Suche mit zwei Kriterien
- Rabatte für die Umsatzstaffel
- Fehlersuche mit dem Klassiker Zahl gegen Text
Die gelb hinterlegten Spalten sind leer. Dort geben Sie die Formeln selbst ein.
Häufig zusammen mit SVERWEIS gebraucht werden die WENN-Funktion und die übrigen zehn wichtigsten Excel-Funktionen.