Der SVERWEIS nach Links

image

Mit der Funktion SVERWEIS kann man in der ersten Spalte einer Matrix nach einem Wert suchen und dann einen beliebigen Wert rechts davon zurückgeben. Was aber wenn wir einen Wert links davon zurückgeben möchten?

Wie sucht man mit einem SVERWEIS nach Links?

Es gibt zwei Lösungen welche hier Abhilfe schaffen. Lösung Nummer 1 (INDEX mit VERGLEICH) ist die einfachste und auch am praktischsten zum Umsetzen. Lösung Nummer 2 ist eine Variante in der wir den SVERWEIS dazu bringen nach Links zu schauen.

Wer die Lösungen selber in einer Datei anschauen möchte kann sich diese hier herunterladen:

Sverweis-auf-der-linken-Seite

 

Lösung 1: INDEX mit VERGLEICH

image

Die Formel in Zelle C9 lautet:

=INDEX(A2:A5;VERGLEICH(A9;C2:C5;0))

Mit dieser Lösung umgehen wir das eigentliche Problem dass der SVERWEIS nur nach rechts suchen kann.

Die VERGLEICH Formel sucht lediglich eine Matrix ab (die nur eine Spaltenbreite hat: C2:C5) und liefert uns die Position in der Matrix wo der gesuchte Text sich befindet (an dritter Stelle ).

Mit der INDEX Formel benutzen wir die Antwort der VERGLEICH Formel um einen Wert zurück zu geben der sich auf der selben Zeile befindet (da die Matrix der Index Formel auf der selben Zeile anfängt und auch nur eine Spalte breit ist).

Dies ist wohl die einfachste und am weitesten verbreitete Lösung für das Problem.

Lösung 2: SVERWEIS mit WAHL

image

Die Formel in Zelle C8 lautet:

=SVERWEIS(A8;WAHL({2\1};A2:A5;B2:B5);2;0)

Die WAHL Formel verstehen

Mit der WAHL Formel lässt sich der SVERWEIS auch dazu bringen auf der linken Seite der Suchspalte nachzuschauen.

Die WAHL Formel wählt einen Wert aus einer Liste von Werten.

Ein Beispiel hilft zum Verständnis:

Wenn wir folgende Formel in Excel eingeben…

=WAHL(2;”A”;”B”;”C”)

…ist das Ergebnis B, weil B den zweiten Wert darstellt.

Wir können aber die Reihenfolge auch überlisten, in dem wir den ersten Wert als B definieren, und den zweiten als A.

Wenn wir folgende Formel…

=WAHL({2\1\3};”A”;”B”;”C”)

…mit F9 auswerten…

={“B”\”A”\”C”}

…so sehen wir dass Excel die Reihenfolge verändert hat!

Diese Eigenschaft nutzen wir aus um dem SVERWEIS glaubhaft zu machen dass die Spalte welche Links von der ersten Spalte steht ,nach der ersten kommt (also rechts wäre).

Deshalb wird die ursprüngliche Formel:

=SVERWEIS(A8;WAHL({2\1};A2:A5;B2:B5);2;0)

zu

=SVERWEIS(A8;{“A34″\”20-123″;”B28″\”20-124″;”K91″\”20-125″;”N32″\”20-126”};2;0)

So liefert uns der SVERWEIS den Wert der Links steht.

Fun Fact

Die Technik mit der verdrehten WAHL Formel ist schon länger bekannt. Ich selber bin erst darauf gestoßen durch ein Artikel auf Chandoo.org. Laut Excelformeln.de hat Klaus “Perry” Pago die Technik mit WAHL am 13.06.2006 entdeckt und WF die INDEX / VERGLEICH Kombination am 22.10.2000. Ich ziehe mein Hut!