Suche…
Einführung
Sucht nach einem Wert in der ersten Spalte eines Tabellenarrays und gibt einen Wert in derselben Zeile aus einer anderen Spalte des Tabellenarrays zurück.
Das V in VLOOKUP steht für vertikal. Verwenden Sie VLOOKUP anstelle von HLOOKUP, wenn sich Ihre Vergleichswerte in einer Spalte links von den Daten befinden, die Sie suchen möchten.
Syntax
- VLOOKUP (Nachschlagewert, Table_array, Col_index_num, Range_lookup)
Parameter
| Parameter | Beschreibung |
|---|---|
| Lookup-Wert | Der Wert, nach dem Sie in der linken Spalte der Tabelle suchen. Kann entweder ein fester Wert, eine Zellenreferenz oder ein benannter Bereich sein ( erforderlich ) |
| table_array | Der Zellenbereich, der aus der Spalte besteht, in der Sie auf der linken Seite suchen möchten, und Werte in den Zellen auf der rechten Seite, die Sie zurückgeben möchten. Kann eine Excel-Zellenreferenz oder ein benannter Bereich sein. ( erforderlich ) |
| col_index_num | Die Nummer der Spalte, aus der Sie Daten zurückgeben möchten, zählt aus der Spalte ganz links in Ihrer Tabelle ( erforderlich ). |
| range_lookup | Steuert die Art der Suche. Bei FALSE oder 0 führt Excel eine genaue Suche aus und gibt nur dann zurück, wenn in der äußersten linken Spalte eine genaue Übereinstimmung vorliegt. Die Beachtung der Rundungsgenauigkeit ist für diese Suche mit numerischen Werten sehr wichtig. Wenn der Wert TRUE oder 1 ist, führt Excel eine Suche durch und approximiert den letzten Wert, den er erreicht oder überschreitet. Daher muss die erste Spalte aufsteigend nach einer ungefähren Suche sortiert werden. range_lookup . ( Optional - Standardeinstellung TRUE) |
Bemerkungen
Ähnliche Funktionen:
- HLOOKUP (das gleiche wie VLOOKUP, sucht aber eher horizontal als vertikal)
- MATCH (wenn Ihr Lookup_value übereinstimmt, wird die Zeilennummer innerhalb des Bereichs zurückgegeben.)
- LOOKUP (ähnlich wie VLOOKUP und MATCH und wird aus Gründen der Abwärtskompatibilität bereitgestellt)
Häufige Fehler:
- Der Parameter " range_lookup" wird nicht festgelegt und das standardmäßige, nicht exakte Übereinstimmungsverhalten wird abgerufen
- Nicht fixiert und absoluter Adressbereich im table_array - beim Kopieren einer Formel wird auch die Referenz "Nachschlagetabelle" verschoben
Verwenden von VLOOKUP, um den Nachnamen einer Person von ihrer Mitarbeiter-ID zu erhalten
Vlookup findet einen Wert in der äußersten linken Spalte eines Bereichs und gibt einen Wert mit einer Anzahl von Spalten rechts und in derselben Zeile zurück.
Angenommen, Sie möchten den Nachnamen der Mitarbeiter-ID 2 aus dieser Tabelle ermitteln:
=VLOOKUP(2,$A$2:$C$4,3,0)
- Der Wert, für den Sie Daten abrufen, ist 2
- Die von Ihnen gesuchte Tabelle befindet sich im Bereich $ A $ 2: $ C $ 4
- Die Spalte, aus der Sie Daten zurückgeben möchten, ist die 3. Spalte von links
- Sie möchten nur Ergebnisse zurückgeben, wenn eine genaue Übereinstimmung vorliegt ( 0 )
Wenn es keine exakte Übereinstimmung mit der Mitarbeiter-ID gibt, gibt VLOOKUP #N/A .
Verwendung von VLOOKUP zum Ermitteln des Bonusprozentsatzes (Beispiel mit dem "Standardverhalten")
In den meisten Fällen wird das range_lookup als FALSE (eine genaue Übereinstimmung) verwendet. Der Standardwert für diesen Parameter ist TRUE - er wird in diesem Formular seltener verwendet, dieses Beispiel zeigt jedoch eine Verwendung. Ein Supermarkt gibt einen Bonus basierend auf den monatlichen Ausgaben der Kunden.
Wenn der Kunde in einem Monat 250 EUR oder mehr ausgibt, erhält er einen Bonus von 1%. 500 EUR oder mehr ergibt 2,5% usw. Natürlich gibt der Kunde nicht immer genau einen der Werte in der Tabelle aus!
=VLOOKUP(261,$A$2:$B$6,2,TRUE)
In diesem Modus findet VLOOKUP den ersten Wert in Spalte A (nach den Bottom-Up-Schritten), der kleiner oder gleich dem Wert 261 -> ist, dh wir erhalten den 1% -Wert zurück. Für diese nicht genaue Übereinstimmung muss die Tabelle in aufsteigender Reihenfolge der ersten Spalte sortiert werden.
- Der Wert, für den Sie Daten abrufen, ist 261
- Die von Ihnen gesuchte Tabelle befindet sich im Bereich $ A $ 2: $ B $ 6
- Die Spalte, aus der Sie Daten zurückgeben möchten, ist die 2. Spalte von links
- Sie möchten nur Ergebnisse zurückgeben, bei denen eine nicht exakte Übereinstimmung vorliegt ( TRUE ). Wir könnten diese TRUE-Einstellung als Standardwert angeben
Verwendung von VLOOKUP mit ungefährer Übereinstimmung.
Wenn der Parameter range_lookup nicht angegeben wird (TRUE oder 1), findet VLOOKUP eine ungefähre Übereinstimmung. Mit "ungefähr" meinen wir, dass VLOOKUP auf den kleinsten Wert passt , der größer als Ihr Lookup_value ist . Beachten Sie, dass Ihr table_array in aufsteigender Reihenfolge nach Suchwerten sortiert werden muss . Ergebnisse sind nicht vorhersagbar, wenn Ihre Werte nicht sortiert werden.
VLOOKUP mit genauem Abgleich verwenden
Die Grundidee von VLOOKUP besteht darin, Informationen in einer Tabellenkalkulationstabelle VLOOKUP und in einer anderen zu platzieren.
Angenommen, dies ist die Tabelle in Tabelle1:
John 12/25/1990
Jane 1/1/2000
Platzieren Sie in Sheet2 John , Andy und Jane in A1, A2 und A3.
In B1, rechts von John , platzierte ich:
=VLOOKUP(A1,Sheet1!$A$1:$B$4,2,FALSE)
Hier ist eine kurze Erklärung der Parameter, die VLOOKUP angegeben wurden. Die A1 bedeutet, ich suche John in A1 von Sheet2. Das
Sheet1!$A$1:$B$4
weist die Funktion an, Sheet1, Spalten A bis B (und Zeilen 1 bis 4) anzusehen. Die Dollarzeichen sind notwendig, um Excel anzuweisen, absolute (statt relative) Verweise zu verwenden. (Relative Verweise würden das Ganze in unerwünschter Weise verschieben, wenn die Formel nach unten kopiert wird.
Die 2 bedeutet, dass die zweite Spalte, das Datum, zurückgegeben wird.
Das FALSE bedeutet, dass Sie eine genaue Übereinstimmung benötigen.
Ich habe dann B1 nach B2 und B3 kopiert. (Am einfachsten ist es, indem Sie auf B1 klicken, um sie zu markieren. Halten Sie dann die Umschalttaste gedrückt und drücken Sie zweimal den Abwärtspfeil. Nun werden B1, B2 und B3 hervorgehoben. Drücken Sie dann Strg-D, um die Formel zu füllen Wenn dies korrekt ausgeführt wird, sollte in B3 dieselbe Formel wie in B1 verwendet werden. Wenn sich die Formeln für die Nachschlagetabelle ändern , z. B. von Sheet1!A1:B3 bis Sheet1:A3:B5 , sollten Sie absolute Referenzen verwenden (mit Dollarzeichen), um die Änderung zu verhindern.)
Hier sind die Ergebnisse:
John 12/25/1990
Andy #N/A
Jane 1/1/2000
Es fand John und Jane und gab ihre Geburtsdaten zurück. Es hat Andy nicht gefunden und zeigt daher ein #N/A .