Excel-SVERWEIS-Funktion

Die Excel-VLOOKUP-Funktion ist ein leistungsstarkes Werkzeug, das Ihnen hilft, einen bestimmten Wert zu finden: Es sucht vertikal in der ersten Spalte einer Tabelle oder eines Bereichs nach einem übereinstimmenden Wert und gibt dann den entsprechenden Wert aus einer anderen Spalte derselben Zeile zurück. Obwohl VLOOKUP äußerst nützlich ist, kann es für Anfänger manchmal schwer verständlich sein. Dieses Tutorial hilft Ihnen dabei, VLOOKUP souverän zu meistern – mit einer schrittweisen Erklärung der Argumente, praktischen Beispielen und Lösungen für häufige Fehler, auf die Sie bei der Verwendung der VLOOKUP-Funktion stoßen könnten.

• Schrittweise Erklärung der Argumente
• Syntax und Argumente
• VLOOKUP-Beispiele
  Exakter Treffer vs. ungefährer Treffer | VLOOKUP mit mehreren Bedingungen
• Häufige VLOOKUP-Fehler & Lösungen
  #NV-Fehler|#WERT-Fehler|#BEZUG-Fehler|Falscher Wert

VLOOKUP-Beispiele.xlsx VLOOKUP-Fehler.xlsx

Verwandte Videos

Schrittweise Erklärung der Argumente

Wie im obigen Screenshot gezeigt, findet die VLOOKUP-Funktion anhand einer vorgegebenen ID-Nummer die zugehörige E-Mail-Adresse. Im Folgenden erläutere ich Schritt für Schritt detailliert, wie VLOOKUP in diesem Beispiel eingesetzt wird – Argument für Argument.

Schritt 1: Starten der VLOOKUP-Funktion

Wählen Sie eine Zelle (in diesem Fall H6) aus, um das Ergebnis anzuzeigen, und starten Sie die VLOOKUP-Funktion, indem Sie den folgenden Inhalt in die Formel-Leiste eingeben.

=VLOOKUP(

Schritt 2: Angeben des Suchwerts

Geben Sie zunächst den Suchwert – also das, wonach Sie suchen – in der VLOOKUP-Funktion an. Hier verweise ich auf die Zelle G6, die die ID-Nummer 1005 enthält.

=VLOOKUP(G6

Schritt 3: Angeben des Tabellenbereichs

Geben Sie als Nächstes einen Zellbereich an, der sowohl den gesuchten als auch den zurückzugebenden Wert enthält. In diesem Fall wähle ich den Bereich B6:E12 aus. Die Formel lautet nun wie folgt:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Schritt 4: Angeben der Spalte, aus der ein Wert zurückgegeben werden soll

Geben Sie anschließend die Spalte an, aus der ein Wert zurückgegeben werden soll.

Da in diesem Beispiel die E-Mail-Adresse anhand einer ID-Nummer ermittelt werden soll, gebe ich hier die Zahl 4 ein, damit VLOOKUP weiß, dass der Wert aus der vierten Spalte des Datenbereichs zurückgegeben werden soll.

=VLOOKUP(G6,B6:E12,4

Schritt 5: Ungefähre oder exakte Übereinstimmung finden

Legen Sie abschließend fest, ob Sie nach einer ungefähren oder einer exakten Übereinstimmung suchen.

• Um einen exakten Treffer zu finden, müssen Sie FALSCH als letztes Argument verwenden.
• Um einen ungefähren Treffer zu finden, verwenden Sie WAHR als letztes Argument oder lassen Sie es einfach leer.

In diesem Beispiel verwende ich FALSCH für eine exakte Übereinstimmung. Die Formel lautet nun wie folgt:

=VLOOKUP(G6,B6:E12,4,FALSE

Drücken Sie die Eingabetaste, um das Ergebnis zu erhalten

Dank der schrittweisen Erläuterung jedes Arguments im obigen Beispiel sind die Syntax und die Argumente der VLOOKUP-Funktion jetzt deutlich leichter verständlich.

Syntax und Argumente

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Suchwert (erforderlich): Der Wert (ein tatsächlicher Wert oder eine Zellreferenz), nach dem gesucht wird. Beachten Sie, dass dieser Wert in der ersten Spalte des Tabellenbereichs stehen muss.
• Tabellenbereich (erforderlich): Ein Zellbereich, der sowohl die Spalte mit dem Suchwert als auch die Spalte mit dem Rückgabewert enthält.
• Spaltenindex (erforderlich): Eine ganze Zahl, die angibt, in welcher Spalte der Rückgabewert steht. Die Zählung beginnt bei 1 für die linkeste Spalte des Tabellenbereichs.
• Bereich_Suche(optional): Ein logischer Wert, der festlegt, ob VLOOKUP einen ungefähren oder exakten Treffer finden soll.
  • Ungefähre Übereinstimmung – Legen Sie dieses Argument auf WAHR, 1 fest oder lassen Sie es leer.
    Wichtig: Damit eine ungefähre Übereinstimmung korrekt funktioniert, müssen die Werte in der ersten Spalte des Tabellenbereichs in aufsteigender Reihenfolge sortiert sein – andernfalls liefert VLOOKUP ein falsches Ergebnis.
  • Exakte Übereinstimmung – Legen Sie dieses Argument auf FALSCH oder 0.

Beispiele

Dieser Abschnitt liefert einige Beispiele, die Ihnen ein umfassenderes Verständnis der VLOOKUP-Funktion vermitteln.

Beispiel 1: Exakte vs. ungefähre Übereinstimmung bei VLOOKUP

Wenn Sie unsicher sind, was bei der Verwendung von VLOOKUP „exakte“ und „ungefähre“ Übereinstimmung bedeuten, hilft Ihnen dieser Abschnitt, diese Unsicherheit zu beseitigen.

Exakte Übereinstimmung bei VLOOKUP

In diesem Beispiel möchte ich die zugehörigen Namen anhand der Punktzahlen im Bereich E6:E8 ermitteln. Dazu gebe ich die folgende Formel in Zelle F6 ein und ziehe den AutoAusfüll-Griff bis F8 herunter. In dieser Formel ist das letzte Argument auf FALSCH festgelegt, um eine Suche mit exakter Übereinstimmung durchzuführen.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Da die Punktzahl 98 jedoch nicht in der ersten Spalte des Datenbereichs vorhanden ist, gibt VLOOKUP den Fehlerwert #NV zurück.

Ungefähre Übereinstimmung bei VLOOKUP

Bleiben wir beim obigen Beispiel: Wenn Sie das letzte Argument auf WAHR setzen, führt VLOOKUP eine Suche mit ungefährer Übereinstimmung durch. Wird keine exakte Übereinstimmung gefunden, sucht es den größten Wert, der kleiner oder gleich dem Suchwert ist, und gibt das zugehörige Ergebnis zurück.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Da die Punktzahl 98 nicht vorhanden ist, sucht VLOOKUP den nächstgrößeren Wert, der kleiner als 98 ist – also 95 – und gibt den zugehörigen Namen als nächstbestes Ergebnis zurück.

Beispiel 2: VLOOKUP mit mehreren Kriterien verwenden

Dieser Abschnitt zeigt, wie Sie VLOOKUP in Excel mit mehreren Bedingungen nutzen können. Wie im folgenden Screenshot dargestellt, ermitteln Sie das Gehalt anhand eines angegebenen Namens (in Zelle H5) und einer Abteilung (in Zelle H6), indem Sie die folgenden Schritte ausführen.

Schritt 1: Hilfsspalte zum Verketten der Werte aus den Suchspalten hinzufügen

In diesem Fall müssen wir eine Hilfsspalte erstellen, um die Werte aus der Name-Spalte und der Abteilung-Spalte zu verketten.

1. Fügen Sie links neben Ihrem Datenbereich eine Hilfsspalte ein und versehen Sie diese mit einer aussagekräftigen Überschrift. Siehe Screenshot:
   
   
2. Wählen Sie in dieser Hilfsspalte die erste Zelle unter der Überschrift aus, geben Sie die folgende Formel in die Formel-Leiste ein und drücken Sie Enter.
   

   =C6&" "&D6

   
   Hinweise: In dieser Formel verwenden wir das kaufmännische Und-Zeichen (&), um den Text aus zwei Spalten zu einem einzigen Textstück zusammenzufügen.

   • C6 ist der Vorname aus der Name-Spalte, die verknüpft werden soll. D6 ist die erste Abteilung aus der Abteilung-Spalte, die verknüpft werden soll.
   • Die Werte dieser beiden Zellen werden mit einem Leerzeichen dazwischen verknüpft.
3. Wählen Sie diese Ergebniszelle aus und ziehen Sie anschließend am AutoAusfüllkästchen nach unten, um die Formel auf weitere Zellen in derselben Spalte anzuwenden.
   
   

Schritt 2: VLOOKUP-Funktion mit den angegebenen Kriterien anwenden

Wählen Sie eine Zelle aus, in der das Ergebnis angezeigt werden soll (hier: I7), geben Sie die folgende Formel in die Formel-Leiste ein und drücken Sie anschließend Eingabe.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Ergebnis

Häufige VLOOKUP-Fehler und Lösungen

Dieser Abschnitt zeigt häufige Fehler bei der Verwendung von VLOOKUP auf und liefert passende Lösungen zur schnellen Behebung.

Zurückgegebener #NV-Fehler

Der häufigste Fehler bei VLOOKUP ist der #NV-Fehler – Excel kann den gesuchten Wert einfach nicht finden. Hier sind einige Gründe, warum VLOOKUP diesen Fehler zurückgeben könnte:

Grund 1: Der Suchwert befindet sich nicht in der ersten Spalte des Tabellenbereichs

Eine wesentliche Einschränkung der Excel-VLOOKUP-Funktion besteht darin, dass die Suche ausschließlich von links nach rechts erfolgen kann – der Suchwertbereich muss sich daher in der ersten Spalte des angegebenen Tabellenbereichs befinden.

Wie im folgenden Screenshot gezeigt, möchte ich einen Namen anhand der angegebenen Berufsbezeichnung ermitteln. Der Suchwert ()Vertriebsleiter) befindet sich dabei in der zweiten Spalte des Tabellenbereichs, während der gewünschte Rückgabewert links davon liegt – weshalb VLOOKUP den Fehler #NV zurückgibt.

Lösungen

Sie können eine der folgenden Lösungen nutzen, um diesen Fehler zu beheben.

• Spalten neu anordnen
  
  Sie können die Spalten so neu anordnen, dass sich die Suchspalte in der ersten Spalte des Tabellenbereichs befindet.
• Verwenden Sie die Funktionen INDEX und VERGLEICH gemeinsam
  
  Hier verwenden wir die Funktionen INDEX und VERGLEICH gemeinsam als Alternative zu VLOOKUP, um dieses Problem zu lösen.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Verwenden Sie die XLOOKUP-Funktion (verfügbar in Excel 365, Excel 2021 und neueren Versionen)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Grund 2: Der Suchwert wurde in der Suchspalte nicht gefunden (exakte Übereinstimmung)

Einer der häufigsten Gründe für den #NV-Fehler bei VLOOKUP ist, dass der gesuchte Wert nicht gefunden wurde.

Wie im folgenden Beispiel gezeigt, möchten wir den Namen anhand der Punktzahl 98 in Zelle E6 ermitteln. Da diese Punktzahl jedoch nicht in der ersten Spalte des Datenbereichs vorhanden ist, gibt VLOOKUP den Fehlerwert #NV zurück.

Lösungen

Um diesen Fehler zu beheben, probieren Sie eine der folgenden Lösungen aus.

• Wenn VLOOKUP den nächstgrößeren Wert finden soll, der kleiner als der Suchwert ist, ändern Sie das letzte Argument von FALSCH (exakter Treffer) auf WAHR (ungefährer Treffer). Weitere Informationen finden Sie unter Beispiel 1: Exakter Treffer vs. ungefährer Treffer mit VLOOKUP.
• Um zu vermeiden, dass das letzte Argument geändert werden muss, und um eine Erinnerung zu erhalten, falls der Suchwert nicht gefunden wird, können Sie die VLOOKUP-Funktion in die WENNFEHLER-Funktion einbetten:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Grund 3: Der Suchwert ist kleiner als der kleinste Wert in der Suchspalte (ungefähre Übereinstimmung)

Wie im folgenden Screenshot gezeigt, führen Sie eine Suche mit ungefährer Übereinstimmung durch. Da der gesuchte Wert – in diesem Fall die ID-Nummer 1001 – kleiner ist als der kleinste Wert (1002) in der Suchspalte, gibt VLOOKUP den Fehler #NV zurück.

Lösungen

Hier sind zwei Lösungen für Sie.

• Stellen Sie sicher, dass der Suchwert größer oder gleich dem kleinsten Wert in der Suchspalte ist.
• Wenn Excel Sie darauf hinweisen soll, dass der Suchwert nicht gefunden wurde, betten Sie die VLOOKUP-Funktion einfach wie folgt in die WENNFEHLER-Funktion ein:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Grund 4: Zahlen sind als Text formatiert

Wie im folgenden Screenshot zu sehen ist, entsteht der #NV-Fehler in diesem Beispiel aufgrund einer Datentyp-Unstimmigkeit zwischen der Suchzelle (G6) und der Suchspalte (B6:B12) der Originaltabelle: Der Wert in G6 ist eine Zahl, während die Werte im Bereich B6:B12 als Text formatierte Zahlen sind.

Lösungen

Um dieses Problem zu lösen, müssen Sie den Suchwert wieder in eine Zahl umwandeln – und dafür haben wir zwei Methoden für Sie.

• Wenden Sie die Funktion „In Zahl umwandeln“ an
  
  Klicken Sie auf die Zelle, deren Text zu Zahl Sie umwandeln möchten, wählen Sie diese Schaltfläche neben der Zelle aus und wählen Sie dann In Zahl umwandeln.
  
• Wenden Sie ein praktisches Werkzeug an, um Umwandlung zwischen Text und Zahl stapelweise durchzuführen
  
  Die Umwandlung zwischen Text und Zahl-Funktion von Kutools für Excelhilft Ihnen dabei, einen Zellbereich problemlos von Text zu Zahl und umgekehrt umzuwandeln.Sichern Sie sich jetzt eine 30-tägige Testversion mit vollem Funktionsumfang!

Grund 5: Der Tabellenbereich ist beim Ziehen der SVERWEIS-Formel in andere Zellen nicht konstant

Wie im folgenden Screenshot zu sehen, befinden sich zwei Suchwerte in den Zellen E6 und E7. Nachdem das erste Ergebnis in F6 ermittelt wurde, ziehen Sie die SVERWEIS-Formel von Zelle F6 nach F7 – es erscheint ein #NV-Fehler. Der Grund: Die Zellbezüge (B6:C12) sind standardmäßig relativ und passen sich beim Herunterziehen automatisch an. Dadurch verschiebt sich der Tabellenbereich auf B7:C13, der den Suchwert 73 nicht mehr enthält.

Lösung

Um den Tabellenbereich konstant zu halten, müssen Sie ihn durch Hinzufügen eines $-Zeichens vor Zeilen und Spalten in den Zellbezügen fixieren. Weitere Informationen zu absoluten Bezügen in Excel finden Sie in diesem Tutorial: Absoluter Bezug in Excel (Erstellung und Verwendung).

Rückgabewert: #WERT!-Fehler

Die folgenden Bedingungen können dazu führen, dass SVERWEIS einen #WERT!-Fehler ausgibt.

Grund 1: Der Suchwert überschreitet 255 Zeichen

Wie im folgenden Screenshot zu sehen ist, überschreitet der Suchwert in Zelle H4 die Grenze von 255 Zeichen – daher gibt SVERWEIS einen #WERT!-Fehler zurück.

Lösungen

Um diese Einschränkung zu umgehen, nutzen Sie einfach eine alternative Suchfunktion, die auch längere Zeichenfolgen problemlos verarbeitet. Testen Sie eine der folgenden Formeln!

• INDEX und VERGLEICH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• XLOOKUP-Funktion(verfügbar in Excel 365, Excel 2021 und neueren Versionen):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Grund 2: Das Argument Spaltenindex ist kleiner als 1

Der Spaltenindex gibt die Spaltennummer innerhalb des Tabellenbereichs an, aus der der gesuchte Wert zurückgegeben werden soll. Dieses Argument muss eine positive Zahl sein, die einer gültigen Spalte im Tabellenbereich entspricht.

Wenn Sie einen Spaltenindex eingeben, der kleiner als 1 ist (also null oder negativ), kann SVERWEIS die entsprechende Spalte im Tabellenbereich nicht finden.

Lösung

Stellen Sie sicher, dass das Spaltenindex-Argument in Ihrer SVERWEIS-Formel eine positive Zahl ist, die einer gültigen Spalte im Tabellenbereich entspricht – so beheben Sie dieses Problem zuverlässig.

Rückgabewert: #BEZUG!-Fehler

Dieser Abschnitt nennt einen Grund, warum SVERWEIS einen #BEZUG!-Fehler verursacht, und liefert Lösungen für dieses Problem.

Grund: Das Spaltenindex-Argument ist größer als die Anzahl der Spalten

Wie im folgenden Screenshot zu sehen ist, umfasst der Tabellenbereich lediglich vier Spalten – der in der SVERWEIS-Formel angegebene Spaltenindex lautet jedoch 5. Da dieser Wert die tatsächliche Anzahl der Spalten im Bereich übersteigt, kann SVERWEIS die referenzierte Spalte nicht finden und liefert schließlich einen #BEZUG!-Fehler zurück.

Lösungen

• Geben Sie eine korrekte Spaltennummer an
  Stellen Sie sicher, dass das Spaltenindex-Argument in Ihrer VLOOKUP-Formel einer gültigen Spalte im Tabellenbereich entspricht.
• Spaltennummer automatisch anhand der angegebenen Spaltenüberschrift ermitteln
  Wenn Ihre Tabelle viele Spalten enthält, kann es schwierig sein, die richtige Spaltenindexnummer zu finden. In diesem Fall können Sie die VERGLEICH-Funktion direkt in VLOOKUP einbetten, um die Spaltenposition anhand einer bestimmten Überschrift automatisch zu bestimmen.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Hinweis: In der obigen Formel ermittelt die Funktion VERGLEICH(„E-Mail",B5:E5,0) die Spaltennummer der Spalte „E-Mail“ innerhalb des Bereichs B6:E12. Das Ergebnis ist hier 4 und wird als Spaltenindex in der VLOOKUP-Funktion verwendet.

Falscher Wert wird zurückgegeben

Wenn SVERWEIS nicht das korrekte Ergebnis liefert, kann dies folgende Ursachen haben:

Grund 1: Die Suchspalte ist nicht in aufsteigender Reihenfolge sortiert

Wenn Sie das letzte Argument auf WAHRfestlegen (oder)leer lassen), um eine ungefähre Übereinstimmung durchzuführen, und die Suchspalte nicht in aufsteigender Reihenfolge sortiert ist, kann das Ergebnis falsch sein.

Lösung

Das Sortieren der Suchspalte in aufsteigender Reihenfolge hilft Ihnen, dieses Problem zu lösen. Gehen Sie dazu wie folgt vor:

1. Wählen Sie die Datenzellen in der Suchspalte aus, wechseln Sie zur Daten-Registerkarte und klicken Sie in der Gruppe Sortieren & Filtern auf Sortieren: Kleinste zu größte.
2. Wählen Sie im Dialogfeld Sortierwarnung die Option Auswahl erweitern aus und klicken Sie auf OK.

Grund 2: Eine Spalte wurde eingefügt oder gelöscht

Wie im folgenden Screenshot zu sehen, befand sich der ursprünglich gesuchte Wert in der vierten Spalte des Tabellenbereichs – daher habe ich den Spaltenindex mit „4“ angegeben. Da zwischenzeitlich eine neue Spalte eingefügt wurde, ist die gewünschte Ergebnisspalte nun die fünfte Spalte des Tabellenbereichs, weshalb SVERWEIS das Ergebnis aus der falschen Spalte liefert.

Lösungen

Hier sind zwei Lösungen für Sie.

• Passen Sie die Spaltenindexnummer manuell so an, dass sie der Position der Rückgabespalte entspricht. Die Formel sollte hier wie folgt angepasst werden:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Wenn Sie stets das Ergebnis aus einer bestimmten Spalte – wie beispielsweise der E-Mail-Spalte in diesem Beispiel – zurückgeben möchten, hilft Ihnen die folgende Formel dabei, den Spaltenindex automatisch anhand der angegebenen Spaltenüberschrift zu ermitteln, und zwar unabhängig davon, ob Spalten in den Tabellenbereich eingefügt oder daraus entfernt wurden.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Weitere Hinweise zur Funktion

• VLOOKUP sucht Werte nur von links nach rechts.
  Der Suchwert muss sich in der linken Spalte befinden, und der Ergebniswert kann sich in einer beliebigen Spalte rechts davon befinden.
• Lassen Sie das letzte Argument leer, verwendet VLOOKUP standardmäßig einen ungefähren Treffer.
• VLOOKUP führt eine Suche durch, die nicht zwischen Groß- und Kleinschreibung unterscheidet.
• Bei mehreren Treffern liefert VLOOKUP ausschließlich den ersten Treffer zurück, den es im Tabellenbereich findet – und zwar entsprechend der Reihenfolge der Zeilen in diesem Bereich.

Verwandte Artikel