Die VLOOKUP-Funktion von Excel wird verwendet, um Informationen aus einer Datentabelle basierend auf einem von Ihnen ausgewählten Suchwert zu suchen und zurückzugeben.
Normalerweise erfordert VLOOKUP, dass sich der Suchwert in der äußersten linken Spalte der Datentabelle befindet, und die Funktion gibt ein anderes Datenfeld in derselben Zeile rechts neben diesem Wert zurück.
Durch Kombinieren von VLOOKUP mit der CHOOSE-Funktion kann jedoch eine linke Suchformel erstellt werden, die:
- Erlaubt, dass der Suchwert aus einer beliebigen Spalte in der Datentabelle stammt
- Gibt Informationen zurück, die sich in einer beliebigen Spalte links vom Nachschlagewert befinden
Hinweis: Diese Anweisungen gelten für die Excel-Versionen 2019, 2016, 2013, 2010 und Excel für Office 365.
01 von 05Lernprogramm: Verwenden der Funktionen VLOOKUP und CHOOSE in einer Left-Lookup-Formel
Verwenden Sie die Formel, um die linke Nachschlagformel zu erstellen, die im Beispielbild zu sehen ist:
= VLOOKUP ($ D $ 2, Select ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
In diesem Beispiel ermöglicht die Formel das Auffinden des Teils, der von den verschiedenen in Spalte 3 der Datentabelle aufgeführten Unternehmen geliefert wird.
Die Aufgabe der CHOOSE-Funktion in der Formel besteht darin, VLOOKUP dazu zu bringen, zu glauben, dass Spalte 3 Spalte 1 ist. Als Ergebnis kann der Firmenname als Nachschlagewert verwendet werden, um den Namen des von den einzelnen Unternehmen gelieferten Teils zu ermitteln.
Geben Sie die Lerndaten ein
- Geben Sie die Überschrift ein Lieferant in die Zelle D1.
- Geben Sie die Überschrift ein Teil in der Zelle E1.
- Geben Sie die in der Abbildung oben dargestellte Datentabelle in Zellen ein D4 bis F9
- Reihen 2 und 3 bleiben leer, um die Suchkriterien und die in diesem Lernprogramm erstellte linke Suchformel aufzunehmen
Öffnen Sie das VLOOKUP-Dialogfeld
Obwohl es möglich ist, die Formel direkt in die Zelle F1 im Arbeitsblatt einzugeben, haben viele Benutzer Schwierigkeiten mit der Syntax der Formel.
In diesem Fall ist es sicherer, das Dialogfeld VLOOKUP zu verwenden. Fast alle Funktionen von Excel verfügen über ein Dialogfeld, in dem Sie die einzelnen Argumente der Funktion in einer separaten Zeile eingeben können.
- Klicken Sie auf Zelle E2 des Arbeitsblatts. E2 ist der Ort, an dem die Ergebnisse der linken Nachschlagformel angezeigt werden.
- Klicke auf das Formeln Registerkarte des Farbbands.
- Klicke auf das Nachschlagen & Nachschlagen Option in der Multifunktionsleiste zum Öffnen der Dropdown-Liste
- Klicke auf VLOOKUP in der Liste, um das Dialogfeld der Funktion anzuzeigen.
Eingabe von Argumenten in das Dialogfeld VLOOKUP
Die Argumente einer Funktion sind die Werte, mit denen die Funktion ein Ergebnis berechnet.
Im Dialogfeld einer Funktion befindet sich der Name jedes Arguments in einer separaten Zeile, gefolgt von einem Feld, in das ein Wert eingegeben werden kann.
Geben Sie die folgenden Werte für jedes der VLOOKUP-Argumente in die richtige Zeile des Dialogfelds ein, wie im nebenstehenden Bild gezeigt.
Der Nachschlagewert
Der Suchwert ist das Informationsfeld, das zum Durchsuchen des Tabellenarrays verwendet wird. VLOOKUP gibt ein anderes Datenfeld aus derselben Zeile wie den Nachschlagewert zurück.
In diesem Beispiel wird eine Zellenreferenz für den Ort verwendet, an dem der Firmenname in das Arbeitsblatt eingegeben wird. Dies hat den Vorteil, dass es einfach ist, den Firmennamen zu ändern, ohne die Formel zu bearbeiten.
- Klicke auf das Lookup-Wert Zeile im Dialogfeld.
- Klicken Sie auf Zelle D2 um diese Zellenreferenz in die Lookup-Wert Linie.
- Drücken Sie die F4 Taste auf der Tastatur, um die Zellreferenz absolut zu machen - $ D $ 2.
Absolute Zellreferenzen
Für die Nachschlagewerte und Tabellenarrayargumente werden absolute Zellverweise verwendet, um Fehler zu vermeiden, wenn die Nachschlagformel in andere Zellen des Arbeitsblatts kopiert wird.
Eingabe der CHOOSE-Funktion
Das Tabellenarrayargument ist der Block zusammenhängender Daten, aus denen bestimmte Informationen abgerufen werden.
Normalerweise sucht VLOOKUP nur nach rechts vom Nachschlagewertargument, um Daten im Tabellenarray zu finden. Um nach links zu schauen, muss VLOOKUP durch das Umordnen der Spalten im Tabellen-Array mit der Funktion CHOOSE ausgetrickst werden.
In dieser Formel erfüllt die Funktion CHOOSE zwei Aufgaben:
- Es erstellt ein Tabellenfeld, das nur zwei Spalten breit ist (Spalten D und F).
- Sie ändert die Reihenfolge der Spalten im Tabellenfeld von rechts nach links, sodass Spalte F an erster Stelle und Spalte D an zweiter Stelle steht.
Funktionen eingeben
Bei der manuellen Eingabe von Funktionen muss jedes Argument der Funktion durch ein Komma getrennt werden.
- Klicken Sie im Dialogfeld VLOOKUP auf die Schaltfläche Table_array Linie.
- Gebe folgendes ein WÄHLEN Funktion:WÄHLEN ({1,2}, $ F: $ F, $ D: $ D)
Spaltenindexnummer
Normalerweise gibt die Spaltenindexnummer an, in welcher Spalte des Tabellen-Arrays die Daten enthalten sind, nach denen Sie suchen. In dieser Formel bezieht es sich jedoch auf die Reihenfolge der Spalten, die von der CHOOSE-Funktion festgelegt wird.
Die CHOOSE-Funktion erstellt ein Tabellenfeld, das zwei Spalten breit ist, wobei Spalte F zuerst von Spalte D gefolgt wird. Da die gesuchten Informationen - der Teilname - in Spalte D sind, muss der Wert des Spaltenindexarguments auf 2 gesetzt werden.
- Klicke auf das Col_index_num Zeile im Dialogfeld.
- Tippe A 2 in dieser Zeile.
Bereichssuche
Das Range_lookup-Argument von VLOOKUP ist ein logischer Wert (nur TRUE oder FALSE), der angibt, ob VLOOKUP eine exakte oder ungefähre Übereinstimmung mit dem Suchwert finden soll.
- Wenn der Wert TRUE ist oder dieses Argument nicht angegeben wird, gibt VLOOKUP entweder eine genaue Übereinstimmung mit dem Wert für Lookup_ zurück, oder, falls keine genaue Übereinstimmung gefunden wird, gibt VLOOKUP den nächstgrößeren Wert zurück. Dazu müssen die Daten in der ersten Spalte von Table_array in aufsteigender Reihenfolge sortiert werden.
- Wenn FALSE, verwendet VLOOKUP nur eine exakte Übereinstimmung mit dem Wert "Lookup_Wert". Wenn in der ersten Spalte von Table_array zwei oder mehr Werte vorhanden sind, die mit dem Suchwert übereinstimmen, wird der erste gefundene Wert verwendet. Wenn keine exakte Übereinstimmung gefunden wird, wird ein Fehler # N / A zurückgegeben.
In diesem Lernprogramm wird Range_lookup auf festgelegt, da wir nach einem bestimmten Teilnamen suchen Falsch so dass nur exakte Übereinstimmungen von der Formel zurückgegeben werden.
- Klicke auf das Range_lookup Zeile im Dialogfeld.
- Schreibe das Wort Falsch Geben Sie in dieser Zeile an, dass VLOOKUP eine exakte Übereinstimmung für die gesuchten Daten zurückgeben soll.
- Klicken OK um die linke Suchformel abzuschließen und das Dialogfeld zu schließen.
- Da wir den Firmennamen noch nicht in Zelle D2 eingegeben haben, wird in Zelle E2 ein Fehler # N / A angezeigt.
Daten mit der linken Nachschlagformel zurückgeben
Um herauszufinden, welche Unternehmen welche Teile liefern, geben Sie den Namen des Unternehmens in die Zelle ein D2 und drücken Sie die EINGEBEN Taste auf der Tastatur.
Der Teilname wird in Zelle E2 angezeigt.
- Klicken Sie auf Zelle D2 in Ihrem Arbeitsblatt.
- Art Gadgets Plus in die Zelle D2 und drücken Sie die EINGEBEN Taste auf der Tastatur.
- Der Text "Gadgets" - der von der Firma Gadgets Plus bereitgestellte Teil - sollte in Zelle E2 angezeigt werden.
Testen Sie die Suchformel weiter, indem Sie andere Firmennamen in Zelle D2 eingeben. Der entsprechende Teilname sollte in Zelle E2 angezeigt werden.
Wenn eine Fehlermeldung wie # N / A in Zelle E2 angezeigt wird, prüfen Sie auf Rechtschreibfehler in Zelle D2.
04 von 05Erstellen eines zweispaltigen Tabellen-Arrays
Die Syntax für die CHOOSE-Funktion lautet:
= CHOOSE (Indexnummer, Wert1, Wert2, … Wert254)
Die CHOOSE-Funktion gibt normalerweise einen Wert aus der Werteliste (Wert1 bis Wert254) basierend auf der eingegebenen Indexnummer zurück.
Wenn die Indexnummer 1 ist, gibt die Funktion Wert1 aus der Liste zurück. Wenn die Indexnummer 2 ist, gibt die Funktion Value2 aus der Liste zurück und so weiter.
Wenn mehrere Indexnummern eingegeben werden, gibt die Funktion mehrere Werte in beliebiger Reihenfolge zurück. Die Auswahl von CHOOSE zur Rückgabe mehrerer Werte erfolgt durch Erstellen eines Arrays.
Um ein Array einzugeben, umgeben Sie die eingegebenen Zahlen mit geschweiften Klammern oder Klammern. Für die Indexnummer werden zwei Nummern eingegeben:{ 1,2 }.
Es sei darauf hingewiesen, dass CHOOSE nicht auf das Erstellen einer zweispaltigen Tabelle beschränkt ist. Durch das Einfügen einer zusätzlichen Zahl in das Array - beispielsweise {1,2,3} - und eines zusätzlichen Bereichs im Argument value wird durch CHOOSE eine dreispaltige Tabelle erstellt.
Mit zusätzlichen Spalten können Sie andere Informationen mit der linken Suchformel zurückgeben, indem Sie das Spaltenindexnummer-Argument von VLOOKUP in die Nummer der Spalte ändern, die die gewünschten Informationen enthält.
05 von 05Ändern der Reihenfolge der Spalten mit der Funktion CHOOSE
In der CHOOSE-Funktion, die in dieser Formel verwendet wird:
WÄHLEN ({1,2}, $ F: $ F, $ D: $ D)
Der Bereich für Spalte F ist vor Spalte D aufgeführt.
Da die CHOOSE-Funktion das Tabellen-Array von VLOOKUP (die Datenquelle für diese Funktion) festlegt, wird das Umschalten der Reihenfolge der Spalten in der CHOOSE-Funktion an VLOOKUP übergeben.
In Bezug auf VLOOKUP ist das Tabellenarray nur zwei Spalten breit, wobei Spalte F links und Spalte D rechts liegen. Da Spalte F den Namen der Firma enthält, nach der gesucht werden soll, und da Spalte D die Teilnamen enthält, kann VLOOKUP seine regulären Suchaufgaben ausführen, um Daten zu finden, die sich links vom Suchwert befinden.
Daher kann VLOOKUP anhand des Firmennamens den von ihnen gelieferten Teil finden.
