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!

Comments

  • Hallo,

    sind sie sicher, dass das so funktioniert? Muss man dafür vorher irgend etwas an den Einstellungen ändern? Ich habe es jetzt mehrfach ausprobiert, aber Excel sagt immer nur “Die Formel enthält einen Fehler” und markiert danach immer den “{2\1}” Teil. Alles andere scheint zu laufen.

    Liegt es am englischen System mit dem ich arbeite? Ich schreibe “=vlookup(A8,choose({2\1},A5:A8,B5:B8),2,0)”.

    KarstenOctober 15, 2012
    • Hi Karsten,
      Nein, man muss an den Einstellungen nichts ändern. Allerdings ist es tatsächlich so dass es mit einer Englischen Excel Version leicht anders funktioniert. Probier mal den \ mit einem , zu ersetzen in der CHOOSE Formel.
      Gruss, Lukas

      Lukas RohrOctober 15, 2012
      • Es funktioniert mit dem Komma!! Vielen Dank. :)

        Auf die Idee wäre ich übrigens nie gekommen, den Back slash durch ein Komma zu ersetzen. ^^

        KarstenOctober 16, 2012
  • Hi Lukas

    Kleine Korrektur zum Fun Fact.
    Wie nachzulesen ist, gibt es die Lösung mit WAHL() seit 13.06.2006 und wurde von Klaus Pago entwickelt.
    Die Lösung mit INDEX()/VERGLEICH() gibt es seit 22.10.2000 und wurde von WF entwickelt.

    DetlefOctober 15, 2012
    • Hi Detlef,
      Da hast Du recht! Ich habe es im Artikel umgehend korrigiert. Danke für den Hinweis!

      Gruss, Lukas

      Lukas RohrOctober 16, 2012
  • Hallo Lukas

    Dieses Forum ist zwar schon ein paar Jahre alt. Ich versuche es trotzdem.

    Gibt es eine Möglichkeit, in der Zeile nach links zu suchen, OHNE Angabe der Anzahl Spalten?

    Anders gesagt:
    Ich möchte in einer Matrix nach einem Wert suchen und in der selben Zeile den Wert aus Spalte A wiedergeben (egal in welcher Spalte sich der Suchwert befindet).

    Gruss
    Andy.

    AndySeptember 25, 2015
  • Backslash?

    =WAHL({2\1\3};”A”;”B”;”C”) ging bei mir nicht ich hab durch
    =WAHL({2.1.3};”A”;”B”;”C”) ersetzen müssen, das ging dann

    AnonymousJuly 7, 2016
    • Je nach Spracheinstellungen / Regionen einstellung in Office können die Trennzeichen unterschiedlich sein. Welches Trennzeichen Dein System benutzt kannst Du herausfinden in dem Du eine Formel eingibst mit einem Bereich und dann mit F9 den Bereich in der Formelleiste auswertest.

      Lukas RohrAugust 2, 2016

Leave a Reply