Linksseitige Suche mit VLOOKUP
In diesem Tutorial erfahren Sie, wie Sie mithilfe der VLOOKUP-Funktion Informationen in der linken Spalte von Excel zu einem bestimmten Element finden, das sich rechts davon befindet. Da die VLOOKUP-Funktion bekanntermaßen nicht nach links suchen kann, benötigen wir zur Lösung dieser Aufgabe die Unterstützung der CHOOSE-Funktion.
Wie führt man mit VLOOKUP eine Suche von links durch?
Um die Informationen zum Produkt 30001 auszufüllen, das in der rechtesten Spalte der Daten (siehe obiger Screenshot) aufgeführt ist, können Sie die CHOOSE-Funktion nutzen, um die Tabelle neu anzuordnen – indem Sie die rechteste Spalte mit dem Suchwert an die linkeste Position „verschieben“. Anschließend verwenden Sie einfach eine normale VLOOKUP-Formel, um den gewünschten Wert abzurufen.
Allgemeine Syntax
=VLOOKUP()lookup_value,CHOOSE(){1,2,3,…,N},rangeN,range1,range2,range3,…),column_num,FALSE)
- Suchwert: Der Wert, mit dem VLOOKUP die Position der zugehörigen Information ermittelt – in diesem Fall die angegebene Produkt-ID.
- Bereich1; Bereich2; Bereich3; BereichN: Die Spalten mit Zellen, in denen der Suchwert und weitere Informationen aufgelistet sind.
- Spaltennummer: Die Zahl gibt an, aus welcher Spalte Sie Daten abrufen möchten. Beachten Sie: Aufgrund der CHOOSE-Funktion wird die bisherige rechte Spalte nun an die erste (linkeste) Position verschoben. Um die 1. Spalte (Farbspalte) anzuzeigen, geben Sie für die Spaltennummer 2 ein; um die 2. Spalte (Größenspalte) anzuzeigen, geben Sie für die Spaltennummer 3 ……
- Bereich_Verweis FALSCH: Erzwingt, dass VLOOKUP nur exakte Übereinstimmungen findet.
Um die Informationen zum Produkt 30001auszufüllen, kopieren oder geben Sie die folgenden Formeln in die entsprechenden Zellen ein und drücken Sie Enter, um die Ergebnisse zu erhalten:
Farbe (Zelle H6)
=VLOOKUP()$H$4;CHOOSE(){1,2,3,4},$E$5:$E$9;$B$5:$B$9;$C$5:$C$9;$D$5:$D$9);2;FALSCH)
Größe (Zelle H7)
=VLOOKUP()$H$4;CHOOSE(){1,2,3,4},$E$5:$E$9;$B$5:$B$9;$C$5:$C$9;$D$5:$D$9);3;FALSCH)
Preis (Zelle H8)
=VLOOKUP()$H$4;CHOOSE(){1,2,3,4},$E$5:$E$9;$B$5:$B$9;$C$5:$C$9;$D$5:$D$9);4;FALSCH)
Hinweis: Anstelle der direkten Eingabe der Produkt-ID 30001in die Formeln verwenden wir den Zellbezug $H$4(wir fügen Dollarzeichen hinzu, um einen absoluten Bezug zu erstellen), da die Formeln dynamisch sein sollen. Auf diese Weise können wir problemlos Informationen zu anderen Produkten abrufen, indem wir lediglich die Produkt-ID in Zelle H3ändern.
Erläuterung der Formel
Im Folgenden dient die unten stehende Formel als Beispiel:
=VLOOKUP()$H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,FALSE)
- CHOOSE({1,2,3,4};$E$5:$E$9;$B$5:$B$9;$C$5:$C$9;$D$5:$D$9):Die CHOOSE-Funktion ordnet die Spalten neu an, indem sie Spalte E an die erste – also linkeste – Position des Arrays verschiebt, und zwar wie folgt:
{30001;"Weiß";„Groß";20;30002;"Schwarz";„Groß";21;30003;"Blau";„Mittel";19;30004;"Rot";„Mittel";18;30005;"Gelb";„Klein";20}.
In Tabellenform sähe das Array dann so aus:
- VLOOKUP()$H$4,CHOOSE({1,2,3,4};$E$5:$E$9;$B$5:$B$9;$C$5:$C$9;$D$5:$D$9),2;FALSCH) = VLOOKUP($H$4 Mit 30001 sucht die VLOOKUP-Funktion den Suchwert in Zelle H4 und ermittelt die Zeile seiner exakten Übereinstimmung in der durch CHOOSE neu erstellten Tabelle, um dann das Ergebnis aus der 2. Spalte zurückzugeben – also Weiß.
Verwandte Funktionen
Die Excel-VLOOKUP-Funktion sucht einen Wert, indem sie die erste Spalte einer Tabelle nach einer Übereinstimmung durchsucht, und gibt den zugehörigen Wert aus einer bestimmten Spalte derselben Zeile zurück.
Die CHOOSE-Funktion gibt einen Wert aus einer Liste von Werten basierend auf einer angegebenen Indexnummer zurück. Beispiel: CHOOSE(3;"Apfel";"Pfirsich";„Orange") gibt „Orange“ zurück, da die Indexnummer 3 ist und „Orange“ der dritte Wert nach der Indexnummer in der Funktion ist.
Verwandte Formeln
Linksseitige Suche mit INDEX und VERGLEICH
Um Informationen in der linken Spalte einer Excel-Tabelle zu einem bestimmten Element zu finden, das sich rechts davon befindet, können Sie die Funktionen INDEX und VERGLEICH kombinieren. Diese leistungsstarke Alternative zu VLOOKUP bietet den entscheidenden Vorteil, dass Sie Werte in beliebigen Spalten suchen können.
Suchwertbereich aus einem anderen Arbeitsblatt oder einer anderen Arbeitsmappe
Wenn Sie wissen, wie die VLOOKUP-Funktion zum Suchen von Werten innerhalb eines Arbeitsblatts verwendet wird, ist das Abrufen von Werten aus einem anderen Arbeitsblatt oder einer anderen Arbeitsmappe kein Problem.
Suche des nächstgelegenen Übereinstimmungswerts mit mehreren Kriterien
In manchen Fällen müssen Sie anhand mehrerer Kriterien den nächstgelegenen oder einen annähernd passenden Wert suchen. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN gelingt Ihnen das in Excel schnell und präzise.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.