Einführung
Einfach ausgedrückt funktioniert die VLOOKUP-Funktion wie eine Indexseite in einem Buch, auf der wir nach dem Thema suchen, um die Seitenzahl zu finden.
Da weltweit täglich mehr Daten produziert werden, sind Nachschlagevorgänge in Datenbanken aller Größen mittlerweile alltäglich. Mit Nachschlagefunktionen können Sie dieser Anforderung in Excel gerecht werden.
Andere Nachschlagefunktionen derselben Familie sind HLOOKUP (horizontale Nachschlagefunktion), LOOKUP und XLOOKUP.
In diesem Artikel geht es um VLOOKUP, was für „vertikale Suche“ steht. Wir zeigen Ihnen in diesem Artikel, wie Sie VLOOKUP verwenden und was es kann und was nicht.
Die Funktion VLOOKUP sucht spaltenübergreifend nach einem definierten Wert.
Die Funktion verwendet als obligatorische Parameter einen Nachschlagewert, eine Tabellenreferenz und einen Spaltenindex. Der letzte Parameter ist optional und gibt den Rückgabewert an. Er kann entweder eine genaue Übereinstimmung oder eine entsprechende Übereinstimmung zurückgeben.
Schauen wir uns die Syntax für VLOOKUP an und verstehen wir sie anhand eines Beispiels.
Syntax
=VLOOKUP (Nachschlagewert, Tabellenarray, Spaltenindexnummer, [Bereichssuche])
Parameter
| Parameter | Erläuterung |
| Nachschlagewert | Dies ist der Wert, nach dem wir in unserer VLOOKUP-Funktion suchen. |
| Tabellenarray | Dieses Argument übernimmt die Start- und Endwerte der Zellen, nach denen die Funktion suchen muss. Das Array, in dem sie sucht. |
| Spaltenindexnummer | Der Wert in diesem Argument stellt den Index der Spalte dar. Dies bedeutet, wie viele Spalten unsere VLOOKUP-Funktion durchsuchen soll. |
| Bereich _lookup | Das letzte Argument akzeptiert zwei Werte, einer ist TRUE und der andere ist FALSE. TRUE gibt die beste Übereinstimmung zurück. FALSE gibt eine genaue Übereinstimmung zurück. |
Beispiel
Im obigen Beispiel haben wir eine Mitarbeitertabelle mit Vorname, Nachname, Alter, Bezeichnung und Gehalt.
Die Tabelle umfasst die Zellen A2 bis F6. Dies bezeichnen wir als Tabellenbereich. Wir verwenden hier VLOOKUP, um das Gehalt des Mitarbeiters basierend auf dem Vornamen des Mitarbeiters zurückzugeben.
Stellen Sie sich dieselbe Situation in einer realen Umgebung vor, in der Sie diese Informationen abrufen müssen, Ihre Tabelle jedoch 5.000 Zeilen statt 5 hat. Sie erkennen schnell, wie hilfreich VLOOKUP in diesem Fall sein kann.
Sehen wir uns nun die Formel an, mit der das Gehalt eines Mitarbeiters mit dem Vornamen Paul ermittelt wird: VLOOKUP(B9, B2:F6, 5, FALSCH) .
Zelle B9 enthält den Lookup-Wert „Paul“. Wir suchen in der Tabelle im Bereich B2 bis F6 nach dem Lookup-Wert „Paul“. Wir setzen die Spaltennummer (column_index_num) auf 5, da sie sich in der 5. Spalte befindet, von der aus auf den Lookup-Wert verwiesen wird.
Der letzte Parameter ist optional. Wir wählen FALSE, da wir eine exakte Übereinstimmung zurückgeben möchten. Wenn wir stattdessen TRUE verwenden, gibt Excel die beste Übereinstimmung zurück, die es finden kann, wenn keine exakte Übereinstimmung gefunden wird.
Die Formel funktioniert folgendermaßen:
VLOOKUP(B9,..,..,..) – übernimmt den Wert in B9, also Paul.
VLOOKUP(B9, B2:F6,..,..) – sucht in den Zellen von B2 bis F6 nach „Paul“. Es findet es in Spalte B in der 5. Zeile.
VLOOKUP(B9, B2:F6, 5,..) – Von Spalte B in der 5. Zeile geht es 5 Spalten nach rechts, da 5 unser Spaltenindex ist.
VLOOKUP(B9, B2:F6, 5, FALSCH) – Excel zieht das Ergebnis aus der 5. Zeile der 5. Spalte (der Gehaltsspalte). Es gibt den Wert 30.000 zurück, was Pauls Gehalt ist.
Verwenden der IF-Bedingung
Sie können die VLOOKUP-Funktion mit einer WENN-Funktion kombinieren, um eine Ausgabemeldung wie „GEFUNDEN“, „NICHT GEFUNDEN“, „VERFÜGBAR“ oder „NICHT VERFÜGBAR“ zurückzugeben.
Sehen wir uns ein Beispiel an, um die Kombination einer WENN-Bedingung mit einer VLOOKUP-Funktion zu verstehen.
=WENN(VLOOKUP(9, A2:F6, 2, FALSCH)=“Jessica”,“Gefunden”,”Nicht gefunden”)
Hier haben wir unsere VLOOKUP-Funktion in eine WENN-Funktion eingebettet. Die WENN-Funktion führt einen logischen Test durch. Das heißt, sie testet etwas anhand der von Ihnen festgelegten Kriterien und gibt eine Ausgabe zurück, wenn WAHR, und eine andere Ausgabe, wenn FALSCH.
In diesem Beispiel besteht unser logischer Test darin, ob das Ergebnis unserer VLOOKUP-Funktion mit der Ausgabe „Jessica“ übereinstimmt. Beachten Sie: Wenn Sie in Excel nach Textwerten suchen oder diese zurückgeben möchten, müssen Sie den Text in Anführungszeichen setzen („TEXT GEHT HIERHIN“).
Wenn unser logischer Test Jessica in unserer VLOOKUP-Funktion findet, wird „Gefunden“ zurückgegeben. Wenn nicht, wird „Nicht gefunden“ zurückgegeben.
Sie können die WENN-Bedingung verwenden, um Fehler in Excel zu behandeln oder einfach die Ausgaben von Funktionen aufzuräumen. Wenn Sie beispielsweise eine lange Liste von Nachschlagevorgängen mit VLOOKUP’s durchführen und viele der Ergebnisse #N/A sind, können Sie WENN verwenden, um eine ansprechendere Ausgabe anzuzeigen. Ebenso ist IFERROR in diesem Fall eine hervorragende Funktion!
Verwenden eines Platzhalterzeichens
Excel hat eine Möglichkeit, Ausnahmen zu behandeln. Diese werden als Platzhalter bezeichnet. Sie können Platzhalter in einem VLOOKUP’s verwenden. Beispielsweise ist „*“ ein Platzhalter, den Sie dem Suchwert hinzufügen können. Sehen wir uns ein Beispiel an.
Wir haben eine Liste von Mitarbeitern und die Details zu ihnen in der Tabelle. Der Vorname und der Nachname sind in der Spalte Name zusammengefasst und wenn wir nach Details zu einem Mitarbeiter suchen und dabei seinen vollständigen Namen verwenden, erhalten wir das Ergebnis.
Sehen Sie sich das folgende Beispiel an. Wenn wir nur einen Teil des Namens angeben, kann die Funktion VLOOKUP’s ihn nicht im Tabellenarray finden und gibt einen #N/A-Fehler zurück.
Hier kommt ein Platzhalterzeichen zur Hilfe. Das Platzhalterzeichen „*“ sucht bei Verwendung in der VLOOKUP’s-Funktion nach einem Wert, der mit dem Namen „Jessica“ beginnt, den Sie in Zelle B8 angegeben haben.
Durch die Kombination des Suchwerts mit unserem Platzhalter-Sternchen erhalten Sie das Ergebnis ohne Fehler.
Lassen Sie uns verstehen, wie es funktioniert. Der Lookup-Wert ist „Jessica“, also B8.
Wenn Sie die Formel als =VLOOKUP(B8, B2:E6,3, FALSE) eingeben , betrachtet die VLOOKUP’s-Formel Zelle B8 und liest den Namen „Jessica“.
Es durchsucht den Bereich B2:E6 und findet keine Übereinstimmung, da die Tabelle den Namen „Jessica Williams“ hat und die Formel als #N/A zurückgibt.
Nehmen wir nun an, wir fügen das Platzhalterzeichen neben dem Zellbezug ein, wie in der Formel = VLOOKUP(B8&”*”, B2:E6, 3, FALSCH) . In diesem Fall erhalten wir das gesuchte Ergebnis, da Excel nach Ergebnissen sucht, die „Jessica“ ähneln, aber nicht genau übereinstimmen.
Unsere VLOOKUP-Funktion gleicht den Suchwert „Jessica“ mit „Jessica Williams“ ab und nimmt den nächsten Parameter „3“, die 3. Spalte aus unserem Suchwert.
Es gibt die Bezeichnung von Jessica als Sekretärin zurück.
Verschachtelte VLOOKUP-Funktion innerhalb der IFNA-Funktion
Durch das Verschachteln einer VLOOKUP’s-Funktion in einer IFNA-Funktion erhalten wir die Flexibilität, sequentielle Nachschlagevorgänge durchzuführen. Wenn beispielsweise die erste VLOOKUP’s-Funktion einen Fehler zurückgibt, ein #N/A, wird dieser von der zweiten VLOOKUP’s-Funktion erkannt.
Ebenso ermöglicht die Formel durch die Verschachtelung mehrerer VLOOKUP’s in der Funktion „WENNFEHLER“ sequenzielle Nachschlagevorgänge.
Sie können Ihre Formel so einrichten, dass Sie eine zweite VLOOKUP’s-Funktion ausführen können, wenn die erste VLOOKUP’s-Funktion eine Fehlermeldung erzeugt. Theoretisch können Sie VLOOKUP’s-Funktionen in Ihrer Formel weiter verschachteln, um mehrere Fehler zu berücksichtigen.
Wenn das verwirrend klingt, keine Sorge, es kann zunächst etwas seltsam sein. Um es zu veranschaulichen, betrachten wir das folgende Beispiel.
In diesem Beispiel suchen wir nach einem Mitarbeiter, dessen Emp_ID 12 ist. Wenn Sie sich die Tabelle ansehen, können Sie sehen, dass diese ID nicht existiert. In diesem Fall können wir ISNA verwenden, um einen anderen Wert zurückzugeben.
Mehr lesen: Samsung Galaxy A34 5G Test: Sollten Sie es kaufen?
Die Formel zur Rückgabe der Meldung „Mitarbeiterdatensatz nicht gefunden“ lautet:
=WENN(ISNA(VLOOKUP(12,A2:F6,2,FALSCH))=WAHR,”Mitarbeiterdatensatz nicht gefunden”,VLOOKUP(12,A2:F6,2,FALSCH)).
Häufig gestellte Fragen
Es gibt mehrere Möglichkeiten, eine VLOOKUP-Funktion auszuführen.
Nachschlagen von Daten im selben Arbeitsblatt und in derselben Arbeitsmappe.
Nachschlagen von Daten in verschiedenen Arbeitsblättern in derselben Arbeitsmappe.
Nachschlagen von Daten in verschiedenen Arbeitsmappen.
Wenn VLOOKUP in verschiedenen Arbeitsblättern derselben Arbeitsmappe verwendet wird, lautet die Syntax:
VLOOKUP(Suchwert, Arbeitsblattname!Zellenbereich, Spaltenindex, Speichersuche])
Wenn VLOOKUP in verschiedenen Arbeitsmappen verwendet wird, lautet die Syntax:
VLOOKUP(Suchwert, [Buch1.xlsx]Arbeitsblattname!Zellenbereich, Spaltenindex, Bereichssuche])
Die Haupteinschränkungen der VLOOKUP-Funktion sind:
VLOOKUP kann nur Werte rechts vom Suchwert nachschlagen. Das heißt, die Suche funktioniert nur von links nach rechts. Für eine Suche von rechts nach links benötigen Sie XLOOKUP oder INDEX MATCH.
VLOOKUP kann nur eine vertikale Suche über Spalten hinweg durchführen. Zeilenübergreifende Suche ist nicht möglich. Für eine horizontale Suche benötigen Sie HVERWEIS oder XLOOKUP.
Eine VLOOKUP-Funktion wird nicht automatisch aktualisiert, wenn eine Formel hinzugefügt wird.
Standardmäßig findet die Funktion VLOOKUP keine genaue Übereinstimmung. Sie gibt #N/A zurück. Um mit VLOOKUP eine ungefähre Übereinstimmung zurückzugeben, müssen Sie den letzten Parameter auf TRUE oder 1 setzen.
Abschließende Gedanken
In diesem Artikel haben wir anhand einiger Beispiele gelernt, wie die VLOOKUP-Funktion funktioniert. Wenn Sie VLOOKUP zum Durchsuchen großer Datenbanken verwenden, empfehlen wir, Ihre Daten in eine Tabelle einzutragen und sicherzustellen, dass Ihre Nachschlagespalte ganz links steht.
In der obigen Tabelle ist beispielsweise die erste Spalte die Mitarbeiter-ID und die restlichen Spalten enthalten Namen, Bezeichnungen und Details zu den Mitarbeitern. Wenn die Abfrage das Eintrittsdatum des Mitarbeiters bei gegebener Mitarbeiter-ID ermitteln soll, funktioniert VLOOKUP gut, da sich die Spalte „Eintrittsdatum“ rechts neben der Mitarbeiter-ID befindet.
Wenn die Tabelle dynamisch ist und weitere Spalten eingefügt werden, ändert VLOOKUP die Formel nicht automatisch. In einer solchen Situation können Sie die Funktion INDEX MATCH verwenden. Weitere Informationen zu den anderen verfügbaren Nachschlagefunktionen finden Sie hier!