Excel VLOOKUP-Funktion

Die Excel VLOOKUP-Funktion ist ein leistungsstarkes Werkzeug, das Ihnen hilft, einen bestimmten Wert zu suchen, indem Sie in der ersten Spalte einer Tabelle oder eines Bereichs vertikal abgleichen und dann einen entsprechenden Wert aus einer anderen Spalte in derselben Zeile zurückgeben. Obwohl VLOOKUP unglaublich nützlich ist, kann es für Anfänger manchmal schwierig zu verstehen sein. Dieses Tutorial soll Ihnen helfen, VLOOKUP zu meistern, indem es eine schrittweise Erklärung der Argumente, nützliche Beispiele und Lösungen für häufige Fehler bietet, die Sie bei der Verwendung der VLOOKUP-Funktion begegnen können.

• Schrittweise Erklärung der Argumente
• Syntax und Argumente
• VLOOKUP-Beispiele
  Exakte Übereinstimmung vs. ungefähre Übereinstimmung | VLOOKUP mit mehreren Bedingungen
• VLOOKUP Häufige Fehler & Lösungen
  #N/A 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, wird die VLOOKUP-Funktion verwendet, um eine E-Mail basierend auf einer gegebenen ID-Nummer zu finden. Ich werde nun eine detaillierte Erklärung geben, wie man VLOOKUP in diesem Beispiel verwendet, indem ich jedes Argument Schritt für Schritt aufschlüssele.

Schritt1: Starten Sie die VLOOKUP-Funktion

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

=VLOOKUP(

Schritt2: Geben Sie den Suchwert an

Geben Sie zuerst den Suchwert an (was Sie suchen) in der VLOOKUP-Funktion. Hier verweise ich auf die Zelle G6, die eine bestimmte ID-Nummer1005 enthält.

=VLOOKUP(G6

Schritt3: Geben Sie das Tabellenarray an

Geben Sie als Nächstes einen Bereich von Zellen an, der sowohl den gesuchten Wert als auch den Wert enthält, den Sie zurückgeben möchten. In diesem Fall wähle ich den Bereich B6:E12 aus. Die Formel sieht nun wie folgt aus:

=VLOOKUP(G6,B6:E12

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

Schritt4: Geben Sie die Spalte an, aus der Sie einen Wert zurückgeben möchten

Geben Sie dann die Spalte an, aus der Sie einen Wert zurückgeben möchten.

In diesem Beispiel, da ich die E-Mail basierend auf einer ID-Nummer zurückgeben muss, gebe ich hier die Zahl4 ein, um VLOOKUP anzuweisen, einen Wert aus der vierten Spalte des Datenbereichs zurückzugeben.

=VLOOKUP(G6,B6:E12,4

Schritt5: Finden Sie eine ungefähre oder eine exakte Übereinstimmung

Bestimmen Sie schließlich, ob Sie nach einer ungefähren oder einer exakten Übereinstimmung suchen.

• Um eine exakte Übereinstimmung zu finden, müssen Sie FALSE als letztes Argument verwenden.
• Um eine ungefähre Übereinstimmung zu finden, verwenden Sie TRUE als letztes Argument oder lassen Sie es einfach leer.

In diesem Beispiel verwende ich FALSE für eine exakte Übereinstimmung. Die Formel sieht nun so aus:

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

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

Durch die Erklärung jedes Arguments einzeln im obigen Beispiel sind die Syntax und die Argumente der VLOOKUP-Funktion nun viel einfacher zu verstehen.

Syntax und Argumente

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

• Lookup_value (erforderlich): Der Wert (ein realer Wert oder eine Zellreferenz), den Sie suchen. Denken Sie daran, dass dieser Wert in der ersten Spalte des table_array sein muss.
• Table_array (erforderlich): Ein Bereich von Zellen enthält sowohl die Spalte des Suchwerts als auch die Spalte des Rückgabewerts.
• Col_index (erforderlich): Eine Ganzzahl, die die Spaltennummer darstellt, die den Rückgabewert enthält. Sie beginnt mit der Nummer1 für die linkeste Spalte des table_array.
• Range_lookup (optional): Ein logischer Wert, der bestimmt, ob Sie möchten, dass VLOOKUP eine ungefähre oder eine exakte Übereinstimmung findet.
  • Ungefähre Übereinstimmung - Setzen Sie dieses Argument auf TRUE, 1 oder lassen Sie es leer.
    Wichtig: Um eine ungefähre Übereinstimmung zu finden, müssen die Werte in der ersten Spalte des table_array in aufsteigender Reihenfolge sortiert sein, falls VLOOKUP das falsche Ergebnis zurückgibt.
  • Exakte Übereinstimmung - Setzen Sie dieses Argument auf FALSE oder0.

Beispiele

Dieser Abschnitt zeigt einige Beispiele, um Ihnen ein umfassenderes Verständnis der VLOOKUP-Funktion zu vermitteln.

Beispiel1: Exakte Übereinstimmung vs. ungefähre Übereinstimmung in VLOOKUP

Wenn Sie bei der Verwendung von VLOOKUP über exakte und ungefähre Übereinstimmung verwirrt sind, kann Ihnen dieser Abschnitt helfen, diese Verwirrung zu klären.

Exakte Übereinstimmung in VLOOKUP

In diesem Beispiel werde ich die entsprechenden Namen basierend auf den in dem Bereich E6:E8 aufgeführten Punktzahlen finden, also gebe ich die folgende Formel in die Zelle F6 ein und ziehe den AutoFill-Griff nach unten bis F8. In dieser Formel ist das letzte Argument als FALSE angegeben, um eine exakte Übereinstimmungssuche durchzuführen.

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

Da jedoch die Punktzahl98 nicht in der ersten Spalte des Datenbereichs existiert, gibt VLOOKUP das Ergebnis #N/A Fehler zurück.

Ungefähre Übereinstimmung in VLOOKUP

Wenn Sie im obigen Beispiel das letzte Argument auf TRUE ändern, führt VLOOKUP eine ungefähre Übereinstimmungssuche durch. Wenn keine Übereinstimmung gefunden wird, wird der nächstgrößere Wert gesucht, der kleiner als der Suchwert ist, und das entsprechende Ergebnis zurückgegeben.

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

Da die Punktzahl98 nicht existiert, findet VLOOKUP den nächstgrößeren Wert, der kleiner als98 ist, nämlich95, und gibt den Namen der Punktzahl95 als das nächstliegende Ergebnis zurück.

Beispiel2: Verwenden Sie VLOOKUP mit mehreren Kriterien

Dieser Abschnitt zeigt, wie Sie VLOOKUP mit mehreren Bedingungen in Excel verwenden. Wie im Screenshot unten gezeigt, wenn Sie versuchen, ein Gehalt basierend auf einem angegebenen Namen (in Zelle H5) und einer Abteilung (in Zelle H6) zu finden, folgen Sie den untenstehenden Schritten, um es zu erledigen.

Schritt1: Fügen Sie eine Hilfsspalte hinzu, um die Werte aus den Suchspalten zu verketten

In diesem Fall müssen wir eine Hilfsspalte erstellen, um die Werte aus der Namensspalte und der Abteilungsspalte zu verketten.

1. Fügen Sie eine Hilfsspalte links von Ihrem Datenbereich hinzu und geben Sie dieser Spalte eine Kopfzeile. Siehe Screenshot:
   
   
2. In dieser Hilfsspalte wählen Sie die erste Zelle unter der Kopfzeile aus, geben Sie die folgende Formel in die Formel-Leiste, und drücken Sie Eingabetaste.
   

   =C6&" "&D6

   
   Hinweise: In dieser Formel verwenden wir ein kaufmännisches Und (&), um den Text in zwei Spalten zu verbinden, um ein einzelnes Textstück zu erzeugen.

   • C6 ist der Vorname der Namensspalte zum Verbinden, D6 ist die erste Abteilung der Abteilungsspalte zum Verbinden.
   • Die Werte dieser beiden Zellen werden mit einem Leerzeichen dazwischen verkettet.
3. Wählen Sie diese Ergebniszelle aus, und ziehen Sie dann den AutoFill-Griff nach unten, um diese Formel auf andere Zellen in derselben Spalte anzuwenden.
   
   

Schritt2: Wenden Sie die VLOOKUP-Funktion mit den gegebenen Kriterien an

Wählen Sie eine Zelle aus, in der Sie das Ergebnis ausgeben möchten (hier wähle ich I7), geben Sie die folgende Formel in die Formel-Leiste ein und drücken Sie dann die Eingabetaste.

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

Ergebnis

Häufige VLOOKUP-Fehler und Lösungen

Dieser Abschnitt listet die häufigen Fehler auf, die Sie bei der Verwendung von VLOOKUP begegnen können, und bietet die Lösungen, um sie zu beheben.

#N/A Fehler wird zurückgegeben

Der häufigste Fehler bei VLOOKUP ist der #N/A Fehler, was bedeutet, dass Excel den gesuchten Wert nicht finden konnte. Hier sind einige Gründe, warum VLOOKUP den #N/A Fehler zurückgeben könnte.

Grund1: Der Suchwert ist nicht in der ersten Spalte des table_array

Eine der Einschränkungen von Excel VLOOKUP ist, dass es Ihnen nur erlaubt, von links nach rechts zu suchen. Daher müssen die Suchwerte in der ersten Spalte des table_array sein.

Wie im Screenshot unten gezeigt, möchte ich einen Namen basierend auf der gegebenen Berufsbezeichnung zurückgeben. Hier befindet sich der Suchwert (Vertriebsleiter) in der zweiten Spalte des table_array und der Rückgabewert ist links von der Suchspalte, daher gibt VLOOKUP den #N/A Fehler zurück.

Lösungen

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

• Ordnen Sie die Spalten neu an
  
  Sie können die Spalten neu anordnen, um die Suchspalte in die erste Spalte des table_array zu setzen.
• Verwenden Sie die INDEX- und MATCH-Funktionen zusammen
  
  Hier verwenden wir die INDEX- und MATCH-Funktionen zusammen 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 Excel365, Excel2021 und späteren Versionen)
  

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

Grund2: Der Suchwert wird in der Suchspalte nicht gefunden (exakte Übereinstimmung)

Einer der häufigsten Gründe, warum VLOOKUP den #N/A Fehler zurückgibt, ist, dass der gesuchte Wert nicht gefunden wird.

Wie im folgenden Beispiel gezeigt, werden wir den Namen basierend auf der gegebenen Punktzahl von98 in E6 finden. Da diese Punktzahl jedoch nicht in der ersten Spalte des Datenbereichs existiert, gibt VLOOKUP das Ergebnis #N/A Fehler zurück.

Lösungen

Um diesen Fehler zu beheben, können Sie eine der folgenden Lösungen ausprobieren.

• Wenn Sie möchten, dass VLOOKUP nach dem nächstgrößeren Wert sucht, der kleiner als der Suchwert ist, ändern Sie das letzte Argument FALSE (exakte Übereinstimmung) zu TRUE (ungefähre Übereinstimmung). Für weitere Informationen siehe Beispiel1: Exakte Übereinstimmung vs. ungefähre Übereinstimmung mit VLOOKUP.
• Um zu vermeiden, das letzte Argument zu ändern und eine Erinnerung zu erhalten, falls der Suchwert nicht gefunden wird, können Sie die VLOOKUP-Funktion in die IFERROR-Funktion einschließen:
  

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

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

Wie im Screenshot unten gezeigt, führen Sie eine ungefähre Übereinstimmungssuche durch. Der gesuchte Wert (die ID-Nummer1001 in diesem Fall) ist kleiner als der kleinste Wert1002 in der Suchspalte, daher gibt VLOOKUP den #N/A Fehler 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 Sie möchten, dass Excel Sie daran erinnert, dass der Suchwert nicht gefunden wurde, verschachteln Sie einfach die VLOOKUP-Funktion in der IFERROR-Funktion wie folgt:
  

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

Grund4: Zahlen sind als Text formatiert

Wie Sie im Screenshot unten sehen können, ist das #N/A Fehlerergebnis in diesem Beispiel auf eine Dateninkonsistenz zwischen der Suchzelle (G6) und der Suchspalte (B6:B12) der Originaltabelle zurückzuführen. Hier ist der Wert in G6 eine Zahl, und die Werte im Bereich B6:B12 sind als Text formatierte Zahlen.

Lösungen

Um dieses Problem zu lösen, müssen Sie den Suchwert zurück in eine Zahl konvertieren. Hier sind zwei Methoden für Sie.

• Verwenden Sie die Funktion In Zahl konvertieren
  
  Klicken Sie auf die Zelle, die Sie von Text in Zahl konvertieren möchten, wählen Sie diese Schaltfläche  neben der Zelle und wählen Sie dann In Zahl konvertieren.
  
• Verwenden Sie ein praktisches Tool, um zwischen Text und Zahl im Batch zu konvertieren
  
  Die Funktion Zwischen Text und Zahl konvertieren von Kutools für Excel hilft Ihnen, einen Bereich von Zellen einfach von Text in Zahl und umgekehrt zu konvertieren. Holen Sie sich jetzt eine30-tägige voll funktionsfähige kostenlose Testversion!

Grund5: Das table_array ist nicht konstant, wenn die VLOOKUP-Formel auf andere Zellen gezogen wird

Wie im Screenshot unten gezeigt, gibt es zwei Suchwerte in E6 und E7. Nachdem das erste Ergebnis in F6 erhalten wurde, ziehen Sie die VLOOKUP-Formel von Zelle F6 nach F7, ein #N/A Fehlerergebnis wird zurückgegeben. Das liegt daran, dass die Zellreferenzen (B6:C12) standardmäßig relativ sind und angepasst werden, wenn Sie durch die Zeilen nach unten gehen. Das Tabellenarray wurde nach unten auf B7:C13 verschoben, das den Suchwert73 nicht mehr enthält.

Lösung

Sie müssen das Tabellenarray sperren, um es konstant zu halten, indem Sie ein $-Zeichen vor den Zeilen und Spalten in den Zellreferenzen hinzufügen. Um mehr über absolute Bezüge in Excel zu erfahren, werfen Sie einen Blick auf dieses Tutorial: Excel absolute Bezug (wie man erstellt und verwendet).

#WERT Fehler wird zurückgegeben

Die folgenden Bedingungen können dazu führen, dass VLOOKUP das Ergebnis #WERT Fehler zurückgibt.

Grund1: Der Suchwert überschreitet255 Zeichen

Wie im Screenshot unten gezeigt, überschreitet der Suchwert in Zelle H4255 Zeichen, daher gibt VLOOKUP ein #WERT Fehlerergebnis zurück.

Lösungen

Um diese Einschränkung zu umgehen, können Sie eine andere Suchfunktion verwenden, die längere Zeichenfolgen verarbeiten kann. Versuchen Sie eine der folgenden Formeln.

• INDEX und MATCH:
  

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

  
• XLOOKUP-Funktion (verfügbar in Excel365, Excel2021 und späteren Versionen):
  

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

Grund2: Das col_index-Argument ist kleiner als1

Der Spaltenindex gibt die Spaltennummer im Tabellenarray an, die den Wert enthält, den Sie zurückgeben möchten. Dieses Argument muss eine positive Zahl sein, die einer gültigen Spalte im Tabellenarray entspricht.

Wenn Sie einen Spaltenindex eingeben, der kleiner als1 ist (d. h. null oder negativ), kann VLOOKUP die Spalte im Tabellenarray nicht finden.

Lösung

Um dieses Problem zu beheben, stellen Sie sicher, dass das Spaltenindex-Argument in Ihrer VLOOKUP-Formel eine positive Zahl ist, die einer gültigen Spalte im Tabellenarray entspricht.

#BEZUG Fehler wird zurückgegeben

Dieser Abschnitt listet einen Grund auf, warum VLOOKUP den #BEZUG Fehler zurückgibt, und bietet Lösungen für dieses Problem.

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

Wie Sie im Screenshot unten sehen können, hat das Tabellenarray nur4 Spalten. Der von Ihnen in der VLOOKUP-Formel angegebene Spaltenindex ist jedoch5, was größer als die Anzahl der Spalten im Tabellenarray ist. Infolgedessen kann VLOOKUP die Spalte nicht finden und gibt letztendlich einen #BEZUG Fehler zurück.

Lösungen

• Geben Sie eine korrekte Spaltennummer an
  Stellen Sie sicher, dass das Spaltenindex-Argument in Ihrer VLOOKUP-Formel eine Zahl ist, die einer gültigen Spalte im Tabellenarray entspricht.
• Erhalten Sie die Spaltennummer automatisch basierend auf der angegebenen Spaltenüberschrift
  Wenn die Tabelle viele Spalten enthält, haben Sie möglicherweise Schwierigkeiten, die richtige Spaltenindexnummer zu bestimmen. Hier können Sie die MATCH-Funktion in der VLOOKUP-Funktion verschachteln, um die Position der Spalte basierend auf einer bestimmten Spaltenüberschrift zu finden.

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

  Hinweis: In der obigen Formel wird die MATCH("Email",B5:E5,0) Funktion verwendet, um die Spaltennummer der "Email"-Spalte im Datenbereich B6:E12 zu erhalten. Hier ist das Ergebnis4, das als col_index in der VLOOKUP-Funktion verwendet wird.

Falscher Wert wird zurückgegeben

Wenn Sie feststellen, dass VLOOKUP nicht das richtige Ergebnis zurückgibt, kann dies durch die folgenden Gründe verursacht werden

Grund1: Die Suchspalte ist nicht in aufsteigender Reihenfolge sortiert

Wenn Sie das letzte Argument auf TRUE gesetzt haben (oder es leer gelassen haben) für eine ungefähre Übereinstimmung, und die Suchspalte nicht in aufsteigender Reihenfolge sortiert ist, kann der resultierende Wert falsch sein.

Lösung

Das Sortieren der Suchspalte in aufsteigender Reihenfolge kann Ihnen helfen, dieses Problem zu lösen. Um dies zu tun, folgen Sie bitte den untenstehenden Schritten:

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

Grund2: Eine Spalte wird eingefügt oder entfernt

Wie im Screenshot unten gezeigt, befindet sich der Wert, den ich ursprünglich zurückgeben wollte, in der vierten Spalte des Tabellenarrays, daher gebe ich die col_index-Nummer als4 an. Da eine neue Spalte eingefügt wird, wird die Ergebnisspalte zur fünften Spalte des Tabellenarrays, was dazu führt, dass VLOOKUP das Ergebnis aus einer falschen Spalte zurückgibt.

Lösungen

Hier sind zwei Lösungen für Sie.

• Sie können die Spaltenindexnummer manuell ändern, um die Position der Rückgabespalte anzupassen. Die Formel hier sollte geändert werden zu:

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

• Wenn Sie immer das Ergebnis aus einer bestimmten Spalte zurückgeben möchten, wie z. B. der Email-Spalte in diesem Beispiel. Die folgende Formel kann helfen, den Spaltenindex automatisch basierend auf der angegebenen Spaltenüberschrift abzugleichen, unabhängig davon, ob Spalten in das Tabellenarray eingefügt oder daraus entfernt werden.

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

Andere Funktionshinweise

• VLOOKUP sucht nur von links nach rechts nach Werten.
  Der Suchwert befindet sich in der linken Spalte, und der Ergebniswert sollte sich in einer beliebigen Spalte rechts von der Suchspalte befinden.
• Wenn Sie das letzte Argument leer lassen, verwendet VLOOKUP standardmäßig die ungefähre Übereinstimmung.
• VLOOKUP führt eine Groß-/Kleinschreibung nicht beachtende Suche durch.
• Bei mehreren Übereinstimmungen gibt VLOOKUP nur die erste Übereinstimmung zurück, die es im Tabellenarray findet, basierend auf der Reihenfolge der Zeilen im Tabellenarray.

Verwandte Artikel