Verwendung der Excel VLOOKUP-Funktion | Exceljet

VLOOKUP ist eine Excel-Funktion zum Abrufen von Daten aus einer vertikal organisierten Tabelle. Suchwerte müssen in der ersten Spalte der an VLOOKUP übergebenen Tabelle erscheinen. VLOOKUP unterstützt ungefähre und genaue Übereinstimmungen sowie Platzhalter (*?) Für Teilübereinstimmungen.

V ist für vertikale

Der Zweck von VLOOKUP besteht darin, Informationen aus einer Tabelle zu erhalten, die wie folgt organisiert ist:

Mit der Bestellnummer in Spalte B als Suchwert kann VLOOKUP die Kunden-ID, den Betrag, den Namen und den Status für jede Bestellung abrufen. Um beispielsweise den Kundennamen für die Bestellung 1004 zu erhalten, lautet die Formel:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

Für horizontale Daten können Sie verwenden HLOOKUP, INDEX und MATCH oder XLOOKUP.

VLOOKUP basiert auf Spaltennummern

Wenn Sie VLOOKUP verwenden, stellen Sie sich vor, dass jede Spalte in der Tabelle beginnend von links nummeriert ist. Geben Sie die entsprechende Nummer als „Spaltenindex“ an, um einen Wert aus einer bestimmten Spalte abzurufen. Der folgende Spaltenindex zum Abrufen des Vornamens lautet beispielsweise 2:

Der Nachname und die E-Mail-Adresse können mit Spalten abgerufen werden 3 und 4:

=VLOOKUP(H3,B4:E13,2,FALSE) // first name=VLOOKUP(H3,B4:E13,3,FALSE) // last name=VLOOKUP(H3,B4:E13,4,FALSE) // email address

VLOOKUP sieht nur richtig aus

VLOOKUP kann nur nach rechts schauen. Die Daten, die Sie abrufen möchten (Ergebniswerte), können in einer beliebigen Spalte rechts neben den Suchwerten angezeigt werden:

If Sie müssen links nach Werten suchen, siehe INDEX und MATCH oder XLOOKUP.

Genaue und ungefähre Übereinstimmung

VLOOKUP verfügt über zwei Übereinstimmungsmodi: exakt und ungefähr. Der Name des Arguments, das den Abgleich steuert, lautet „range_lookup“. Dies ist ein verwirrender Name, da er etwas mit Zellbereichen wie A1: A10 zu tun zu haben scheint. Tatsächlich bezieht sich das Wort „Bereich“ in diesem Fall auf „Wertebereich“. Wenn range_lookup TRUE ist, stimmt VLOOKUP eher mit einem Wertebereich als mit einem exakten Wert überein. Ein gutes Beispiel hierfür ist die Verwendung von VLOOKUP zur Berechnung der Noten.

Es ist wichtig zu verstehen, dass range_lookup standardmäßig TRUE ist. Dies bedeutet, dass VLOOKUP standardmäßig eine ungefähre Übereinstimmung verwendet, was gefährlich sein kann. Setzen Sie range_lookup auf FALSE, um eine genaue Übereinstimmung zu erzwingen:

Hinweis: Sie können für eine genaue Übereinstimmung auch Null (0) anstelle von FALSE angeben.

Genaue Übereinstimmung

In den meisten Fällen möchten Sie VLOOKUP wahrscheinlich im Modus für exakte Übereinstimmungen verwenden. Dies ist sinnvoll, wenn Sie einen eindeutigen Schlüssel als Suchwert verwenden können, z. B. den Filmtitel in diesen Daten:

Die Formel in H6 zum Finden des Jahres, basierend auf einer genauen Übereinstimmung des Filmtitels, lautet:

=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

Ungefähre Übereinstimmung

In Fällen, in denen Sie die beste Übereinstimmung wünschen, nicht unbedingt eine genaue Übereinstimmung, möchten Sie den ungefähren Modus verwenden. Im Folgenden möchten wir beispielsweise einen Provisionssatz in der Tabelle G5: H10 nachschlagen. Die Suchwerte stammen aus Spalte C. In diesem Beispiel müssen wir VLOOKUP im ungefähren Übereinstimmungsmodus verwenden, da in den meisten Fällen keine genaue Übereinstimmung gefunden wird. Die VLOOKUP-Formel in D5 ist so konfiguriert, dass eine ungefähre Übereinstimmung durchgeführt wird, indem das letzte Argument auf TRUE gesetzt wird:

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

VLOOKUP durchsucht die Werte in Spalte G nach dem Suchwert. Wenn eine genaue Übereinstimmung gefunden wird, verwendet VLOOKUP diese. Wenn nicht, tritt VLOOKUP „zurück“ und stimmt mit der vorherigen Zeile überein.

Hinweis: Wenn Sie den ungefähren Übereinstimmungsmodus mit VLOOKUP verwenden, müssen die Daten in aufsteigender Reihenfolge nach dem Suchwert sortiert werden.

Erste Übereinstimmung

Bei doppelten Werten findet VLOOKUP die erste Übereinstimmung, wenn der Übereinstimmungsmodus genau ist. Im folgenden Bildschirm ist VLOOKUP so konfiguriert, dass der Preis für die Farbe „Grün“ ermittelt wird. Es gibt drei Einträge mit der Farbe Grün, und VLOOKUP gibt den Preis für den ersten Eintrag zurück, 17 USD. Die Formel in Zelle F5 lautet:

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

Platzhalterübereinstimmung

Die VLOOKUP-Funktion unterstützt Platzhalter, mit denen eine teilweise Übereinstimmung mit einem Suchwert durchgeführt werden kann. Sie können beispielsweise VLOOKUP verwenden, um Werte aus einer Tabelle abzurufen, nachdem Sie nur einen Teil eines Suchwerts eingegeben haben. Um Platzhalter mit VLOOKUP zu verwenden, müssen Sie den genauen Übereinstimmungsmodus angeben, indem Sie für das letzte Argument, range_lookup, FALSE oder 0 angeben. Die Formel in H7 ruft den Vornamen „Michael“ ab, nachdem „Aya“ in Zelle H4 eingegeben wurde:

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

Lesen Sie hier eine ausführlichere Erklärung.

Zwei-Wege-Suche

In der VLOOKUP-Funktion befindet sich die Spalte Das Indexargument ist normalerweise als statische Zahl fest codiert.Sie können jedoch auch einen dynamischen Spaltenindex erstellen, indem Sie die MATCH-Funktion verwenden, um die richtige Spalte zu finden. Mit dieser Technik können Sie eine dynamische bidirektionale Suche erstellen, die sowohl für Zeilen als auch für Spalten übereinstimmt. Im folgenden Bildschirm ist VLOOKUP so konfiguriert, dass eine Suche basierend auf Name und Monat durchgeführt wird. Die Formel in H6 lautet:

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

Weitere Informationen finden Sie in diesem Beispiel.

Hinweis: Im Allgemeinen sind INDEX und MATCH eine flexiblere Möglichkeit, bidirektionale Suchvorgänge durchzuführen.

Mehrere Kriterien

Die VLOOKUP-Funktion verarbeitet nicht mehrere Kriterien nativ. Sie können jedoch eine Hilfsspalte verwenden, um mehrere Felder miteinander zu verbinden, und diese Felder wie mehrere Kriterien in VLOOKUP verwenden. Im folgenden Beispiel ist Spalte B eine Hilfsspalte, die Vor- und Nachnamen zusammen mit dieser Formel verkettet:

=C5&D5 // helper column

VLOOKUP ist so konfiguriert, dass sie dasselbe tun, um einen Suchwert zu erstellen. Die Formel in H6 lautet:

=VLOOKUP(H4&H5,B5:E13,4,0)

Weitere Informationen finden Sie in diesem Beispiel.

Hinweis: INDEX, MATCH und XLOOKUP sind robustere Methoden zum Behandeln von Suchvorgängen, die auf mehreren Kriterien basieren.

VLOOKUP- und # N / A-Fehler

Wenn Sie VLOOKUP verwenden, wird unweigerlich der Fehler # N / A auftreten. Der Fehler # N / A bedeutet nur „nicht gefunden“. Im folgenden Bildschirm ist beispielsweise der Nachschlagewert „Toy Story 2“ in der Nachschlagetabelle nicht vorhanden, und alle drei VLOOKUP-Formeln geben # N / A zurück:

Eine Möglichkeit, den NA-Fehler zu „fangen“, besteht darin, die IFNA-Funktion wie folgt zu verwenden:

Die Formel in H6 lautet:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

Die Nachricht kann nach Wunsch angepasst werden. Um nichts zurückzugeben (dh ein leeres Ergebnis anzuzeigen), wenn VLOOKUP # N / A zurückgibt, können Sie eine leere Zeichenfolge wie die folgende verwenden:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

Der Fehler # N / A ist nützlich, weil er Ihnen sagt, dass etwas nicht stimmt. In der Praxis gibt es viele Gründe, warum dieser Fehler möglicherweise auftritt, darunter:

Der Suchwert ist in der Tabelle nicht vorhanden.
Der Suchwert ist falsch geschrieben oder enthält Zusätzlicher Speicherplatz
Der Übereinstimmungsmodus ist genau, sollte jedoch ungefähr sein.
Der Tabellenbereich ist nicht korrekt eingegeben.
Sie kopieren VLOOKUP und die Tabellenreferenz nicht gesperrt

Lesen Sie mehr: VLOOKUP ohne # N / A-Fehler

Mehr über VLOOKUP

Weitere VLOOKUP-Beispiele
VLOOKUP-Videos
23 Tipps zur Verwendung von VLOOKUP

Weitere Hinweise

Range_lookup steuert, ob der Wert genau übereinstimmen muss oder nicht. Der Standardwert ist TRUE = nicht exakte Übereinstimmung zulassen.
Setzen Sie range_lookup auf FALSE, um eine exakte Übereinstimmung zu erfordern, und TRUE, um eine nicht exakte Übereinstimmung zuzulassen.
Wenn range_lookup TRUE ist (die Standardeinstellung) führt eine nicht exakte Übereinstimmung dazu, dass die VLOOKUP-Funktion mit dem nächsten Wert in der Tabelle übereinstimmt, der immer noch kleiner als der Wert ist.
Wenn range_lookup weggelassen wird, ermöglicht die VLOOKUP-Funktion eine nicht exakte Übereinstimmung Übereinstimmung, aber es wird eine genaue Übereinstimmung verwendet, falls eine vorhanden ist.
Wenn range_lookup TRUE ist (Standardeinstellung), stellen Sie sicher, dass die Suchwerte in der ersten Zeile der Tabelle in aufsteigender Reihenfolge sortiert sind. Andernfalls gibt VLOOKUP möglicherweise einen falschen oder unerwarteten Wert zurück.
Wenn range_lookup FALSE ist (genaue Übereinstimmung erforderlich), müssen die Werte in der ersten Spalte der Tabelle nicht sortiert werden.

Excel VLOOKUP-Funktion