Generische Formel
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Zusammenfassung
Um einen VLOOKUP umzukehren, dh den ursprünglichen Suchwert mithilfe eines VLOOKUP-Formelergebnisses zu ermitteln, können Sie eine knifflige Formel verwenden, die auf der CHOOSE-Funktion basiert, oder einfachere Formeln, die auf INDEX und MATCH oder XLOOKUP basieren, wie unten erläutert. In dem gezeigten Beispiel lautet die Formel in H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Bei diesem Setup findet VLOOKUP die Option, die mit Kosten von 3000 verbunden ist, und gibt "C" zurück.
Hinweis: Dies ist ein fortgeschritteneres Thema. Wenn Sie gerade erst mit VLOOKUP beginnen, beginnen Sie hier.
Einführung
Eine wesentliche Einschränkung von VLOOKUP besteht darin, dass nur rechts nach Werten gesucht werden kann. Mit anderen Worten, die Spalte mit den Suchwerten muss sich links von den Werten befinden, die Sie mit VLOOKUP abrufen möchten. Daher gibt es bei der Standardkonfiguration keine Möglichkeit, VLOOKUP zu verwenden, um "nach links zu schauen" und die ursprüngliche Suche umzukehren.
Vom Standpunkt von VLOOKUP aus können wir das Problem folgendermaßen visualisieren:
Die unten erläuterte Problemumgehung verwendet die CHOOSE-Funktion, um die Tabelle in VLOOKUP neu anzuordnen.
Erläuterung
Beginnend ist die Formel in H5 eine normale VLOOKUP-Formel:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Unter Verwendung von G5 als Suchwert ("C") und der Daten in B5: D8 als Tabellenarray führt VLOOKUP eine Suche nach Werten in Spalte B durch und gibt den entsprechenden Wert aus Spalte 3 (Spalte D), 3000 zurück. Hinweis Als letztes Argument wird Null (0) angegeben, um eine genaue Übereinstimmung zu erzwingen.
Die Formel in G10 zieht einfach das Ergebnis von H5:
=H5 // 3000
Um eine umgekehrte Suche durchzuführen, lautet die Formel in H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Das schwierige Bit ist die CHOOSE-Funktion, mit der das Tabellenarray neu angeordnet wird, sodass Cost die erste Spalte und Option die letzte ist:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Die CHOOSE-Funktion dient zum Auswählen eines Werts basierend auf einem numerischen Index. In diesem Fall liefern wir drei Indexwerte in einer Array-Konstante:
(3,2,1) // array constant
Mit anderen Worten, wir fragen nach Spalte 3, dann nach Spalte 2 und dann nach Spalte 1. Darauf folgen die drei Bereiche, die jede Spalte der Tabelle in der Reihenfolge darstellen, in der sie auf dem Arbeitsblatt erscheinen.
Mit dieser Konfiguration gibt CHOOSE alle drei Spalten in einem einzigen 2D-Array wie folgt zurück:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Wenn wir dieses Array als Tabelle im Arbeitsblatt visualisieren, haben wir:
Hinweis: Die Überschriften sind nicht Teil des Arrays und werden hier nur zur Verdeutlichung angezeigt.
Tatsächlich haben wir die Spalten 1 und 3 vertauscht. Die reorganisierte Tabelle wird direkt an VLOOKUP zurückgegeben, das mit 3000 übereinstimmt, und gibt den entsprechenden Wert aus Spalte 3 "C" zurück.
Mit INDEX und MATCH
Die obige Lösung funktioniert gut, ist jedoch schwer zu empfehlen, da die meisten Benutzer die Funktionsweise der Formel nicht verstehen. Eine bessere Lösung ist INDEX und MATCH mit einer Formel wie der folgenden:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Hier findet die MATCH-Funktion den Wert 3000 in D5: D8 und gibt ihre Position 3 zurück:
MATCH(G10,D5:D8,0) // returns 3
Hinweis: MATCH wird für eine genaue Übereinstimmung konfiguriert, indem das letzte Argument auf Null (0) gesetzt wird.
MATCH gibt ein Ergebnis als Zeilennummer direkt an INDEX zurück, sodass die Formel wie folgt lautet:
=INDEX(B5:B8,3) // returns "C"
und INDEX gibt den Wert aus der dritten Zeile von B5 zurück: B8, "C".
Diese Formel zeigt, wie INDEX und MATCH flexibler sein können als VLOOKUP.
Mit XLOOKUP
XLOOKUP bietet auch eine sehr gute Lösung. Die äquivalente Formel lautet:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Mit einem Suchwert von G10 (3000), einem Suchfeld von D5: D8 (Kosten) und einem Ergebnisfeld von B5: B8 (Optionen) sucht XLOOKUP das 3000 im Suchfeld und gibt das entsprechende Element aus dem Ergebnisfeld zurück. "C". Da XLOOKUP standardmäßig eine exakte Übereinstimmung ausführt, muss der Übereinstimmungsmodus nicht explizit festgelegt werden.
