Sam verwendet die VLOOKUP-Funktion häufig. Es ist sehr praktisch, aber es gibt eine schwerwiegende Einschränkung: Die Suche kann nur auf Spalten rechts verweisen. Dies bedeutet, dass Sam als dritten Parameter für VLOOKUP keinen negativen Wert verwenden kann, um auf eine Spalte links zu verweisen. Er fragt sich, ob es einen Weg gibt, diese Einschränkung zu umgehen.

Es gibt tatsächlich drei Möglichkeiten, um diese Einschränkung zu umgehen: Umstrukturierung, Verwendung von INDEX und Verwendung von CHOOSE. Ich werde mir nacheinander jede dieser Methoden ansehen.

Restrukturierung Ihrer Inhalte

Dies mag der am wenigsten wünschenswerte Ansatz sein, aber ich werde ihn ganz vorne aus dem Weg räumen. Wenn Sie feststellen, dass Sie häufig Werte links von Ihrer Suche zurückgeben müssen, können Sie Ihr Arbeitsblatt so umstrukturieren, dass sich die Werte rechts von Ihrer Formel befinden.

Eine alternative Methode zur Umstrukturierung besteht darin, eine Hilfsspalte rechts von Ihrer Formel zu verwenden. Diese Hilfsspalte muss sich lediglich auf die tatsächlichen Rückgabewerte beziehen. Wenn sich Ihre Rückgabewerte beispielsweise in Spalte A und Ihre Formel in Spalte E befinden, können Sie eine Hilfsspalte a J hinzufügen.

Die Formel in J1 wäre einfach = A1. Kopieren Sie es nach unten und verwenden Sie dann Spalte J als Rückgabewerte in den Formeln in Spalte E.

Verwenden von INDEX und MATCH

Der vielleicht häufigste Ansatz für das Problem, das Sam hat, ist die Verwendung einer Kombination der INDEX- und MATCH-Funktionen anstelle von VLOOKUP. Angenommen, Sie haben die folgende VLOOKUP-Formel:

=VLOOKUP(G1,$C$1:$E$100,3,TRUE)

Dies sucht im Bereich C1: C100 den Wert in G1 (als ungefähre Übereinstimmung) und erhält den entsprechenden Wert in E1: E100. Diese Formel entspricht der folgenden Formel:

=INDEX($E$1:$E$100,MATCH(G1,$C$1:$C$100,1))

Wenn Sie also eine Spalte links von Ihrer Suchspalte erhalten möchten (z. B. A1: A100), können Sie Folgendes verwenden:

=INDEX($A$1:$A$100,MATCH(G1,$C$1:$C$100,1))

Wenn Sie eine genaue Übereinstimmung wünschen, die von der Suche zurückgegeben wird, müssen Sie lediglich die letzte 1 in der MATCH-Funktion in eine 0 ändern, wie folgt:

=INDEX($A$1:$A$100,MATCH(G1,$C$1:$C$100,0))

Verwenden von VLOOKUP und CHOOSE

Wenn Sie die VLOOKUP-Funktion in Ihrer Formel weiterhin verwenden möchten, können Sie sie zum Abrufen von Werten auf der linken Seite „täuschen“, indem Sie auch die CHOOSE-Funktion einschließen.

Um dies zu veranschaulichen, nehmen wir an, dass sich Ihre Suchwerte in Spalte D und die Rückgabewerte in Spalte A befinden. In Zelle G1 befindet sich Ihr Suchwert. Die folgende Formel gibt die richtigen Werte zurück:

=VLOOKUP(G1,CHOOSE({1,2},$D$1:$D$100,$A$1:$A$100),2,FALSE)

Die CHOOSE-Funktion gibt ein Array zurück, das aus den angegebenen Zellen besteht.

Die VLOOKUP-Funktion gibt dann einen Wert aus der zweiten Spalte dieses Arrays, die zufällig Spalte A ist, links von Spalte D zurück.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13608) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.