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:
Lösung 1: INDEX mit VERGLEICH
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
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!
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)”.
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
Es funktioniert mit dem Komma!! Vielen Dank. :)
Auf die Idee wäre ich übrigens nie gekommen, den Back slash durch ein Komma zu ersetzen. ^^
Glad to help!
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.
Hi Detlef,
Da hast Du recht! Ich habe es im Artikel umgehend korrigiert. Danke für den Hinweis!
Gruss, Lukas
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.
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
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.
Dieses Forum ist zwar schon älter und ich bin blutige Anfängerin, aber ich frage trotzdem:
Ich habe einen SVERWEIS mit WAHL erstellt – bei Spalte D gebe ich den Namen ein und in Spalte C erscheint die Station, auf der dieser Patient liegt. Wenn ich jetzt am Ende der bisher eingegebenen Namen in Spalte D ankomme und einen neuen einfügen möchte, wird die Station nicht automatisch ausgefüllt. SVERWEIS funktioniert also nicht, da die Zelle scheinbar keine Funktion hat.
Wie kann ich das fortlaufend einsetzen. Ich hab mich schon ein wenig mit dem Spaltenindex gespielt, aber keine Ahnung wie ich weiterkomme.
Danke für eine Antwort, hoffentlich :)
LG
Schade.
Ich hätte einen WVerweis gesucht dessen Suchrichtung von rechts nach links ist.
In meiner horizontal aufgebauten Liste kann der Suchterm mehrfach vorkommen, aber das aktuellste Ergebnis steht am weitesten rechts.
Wahrscheinlich bin ich schneller, wenn ich ein Makro programmiere ;-).
Oder einfach einen LinksVerweis schreiben = LVerweis – ist aber nur ein Kinkerlitzchen
Public Function LVerweis(searchcriteria As Variant, matrix As Range, columnindex As Long, Optional NA As Boolean, Optional typ As Byte) As Variant
Dim wbRng As Workbook
Dim wsRng As Worksheet
Dim StaCel, EndCel, Matchrng, MatchCell As Range
Dim CntCols, StaRow, EndRow, StaCol, EndCol As Long
If typ 1 Then
LVerweis = CVErr(xlErrRef)
Exit Function
End If
CntCols = matrix.Columns.Count
If CntCols < columnindex Or columnindex < 1 Then
LVerweis = CVErr(xlErrRef)
Exit Function
End If
Set wbRng = Workbooks(matrix.Parent.Parent.Name)
Set wsRng = wbRng.Sheets(matrix.Parent.Name)
Set StaCel = wsRng.Range(matrix(1).Address)
Set EndCel = wsRng.Range(matrix(matrix.Cells.Count).Address)
StaRow = StaCel.Row
StaCol = StaCel.Column
EndRow = EndCel.Row
EndCol = EndCel.Column
Set Matchrng = Range(wsRng.Cells(StaRow, EndCol), wsRng.Cells(EndRow, EndCol))
If IsError(Application.VLookup(searchcriteria, Matchrng, 1, 0)) Then
LVerweis = CVErr(xlErrNA)
Exit Function
End If
Set MatchCell = wsRng.Cells(WorksheetFunction.Match(searchcriteria, Matchrng, 0) + StaRow – 1, EndCol)
If typ = 0 Then
If IsError(MatchCell.Offset(0, (columnindex * -1) + 1).Value) Then
LVerweis = MatchCell.Offset(0, (columnindex * -1) + 1).Value
Exit Function
End If
LVerweis = MatchCell.Offset(0, (columnindex * -1) + 1)
If LVerweis = vbNullString Then
LVerweis = vbNullString
End If
ElseIf typ = 1 Then
Set MatchCell = Range(MatchCell.Offset(0, (columnindex * -1) + 1).Address)
LVerweis = MatchCell.Address
End If
End Function
:-) per VBA geht auch! Danke für den Beitrag!
Dank neuem XVERWEIS hat sich das Problem inzwischen gelöst