Mit der Excel-Funktion SVERWEIS nach links gehen

Ein allgemeines Ärgernis für viele Excel-Anwender ist im Zusammenhang mit der Funktion SVERWEIS, dass bei der Suche in der Matrix nur nach rechts, nicht aber nach links gegangen werden kann.

Bekannt ist aber, dass das Auffinden des gesuchten Wertes stattdessen z.B. mit der Funktion INDEX vorgenommen werden kann.

Dennoch gibt es wohl sehr viele Nutzer, die unbedingt mit SVERWEIS arbeiten möchten. Der Beitrag zeigt eine bekannte und eine neue Lösung.

1. Eine bekannte Lösung

Kluge Excel-Praktiker haben sich Gedanken gemacht und Lösungen gefunden.
So findest du eine Lösung bei Excelformeln.de [1] unter Zuhilfenahme der Funktion WAHL.

So geht es:
Eine kleine Tabelle enthält vier Namen und links (!) davon beliebige Nummern.
In Zelle P19 wird das Suchkriterium, ein Name, eingegeben.

Mit SVERWEIS soll die zugehörige Nummer links davon gefunden und in Zelle R19 eingetragen werden.

Suche nach links, das geht mit SVERWEIS nicht.

Der Schöpfer der Lösung wendet nun einen Trick an und bestimmt mit der WAHL-Funktion die Matrix.

Genial, wie ich denke, setzt er den Index (2 Indizes) von WAHL in geschweifte Klammern. Damit wird zuerst das zweite Wertargument, der Bereich N19:N22, nach dem Suchkriterium durchsucht.

Im zweiten Schritt wird dann die gesuchte Nummer im Bereich M19:M22 gesucht und ausgegeben.

Dies ist die Formel:

=SVERWEIS($P$19;WAHL({2.1};M19:M22;N19:N22);2;FALSCH)

Ergebnis:

Diese Lösung beschreibt ebenfalls Lukas Rohr von ExcelNova.org [2].

Er gibt zusätzlich noch ein wenig Aufklärung über die Herkunft der Idee.
Den darin erwähnten Chandoo-Artikel findest du hier [3].

2. Eine neue Lösung

Das Problem hat mich nicht losgelassen und so will ich eine weitere Lösung vorstellen.

Die Syntax von SVERWEIS bleibt grundsätzlich erhalten. Folgende Tabelle ist Grundlage für den Versuch.

Den Rang in Spalte O ermittelst du mit dieser Formel:

=RANG.GLEICH(N30;$N$30:$N$49;0)

Zunächst der klassische SVERWEIS:

Das Suchkriterium ist (Rang) 1, die Eingabe erfolgt in N52. In N54 soll das zugehörige Produkt ausgegeben werden.

Das erreichst du mit der Formel

=SVERWEIS($N$52;O30:P49;2;FALSCH)

Mein Vorschlag beruht nun darauf, das Suchkriterium im SVERWEIS mit der Funktion BEREICH.VERSCHIEBEN zum Wert in der ersten Spalte der Matrix zu verlagern.

Dadurch findet SVERWEIS das Suchkriterium immer in der ersten Spalte der gesamten Matrix M30:P49 und kann somit entsprechend dem Spaltenindex immer nach rechts wandern.

Das Suchkriterium definiert sich durch diese Formel:

=BEREICH.VERSCHIEBEN(M29;VERGLEICH(N52;O30:O49;0);)

Ausgehend von M29 geht BEREICH.VERSCHIEBEN um so viele Zeilen nach unten, wie VERGLEICH als Position von Rang 1 vorgibt, in diesem Fall 12.

BEREICH.VERSCHIEBEN bleibt in der Spalte M, das Argument Spalte bleibt leer.

Als Ergebnis erhältst du 42959

Formatiere den Wert als Datum “TT.MM.JJJJ”. Das Ersatz-Suchkriterium ist nun der “12.08.2017”, das Datum, das zu Rang 1 gehört.

Nun kannst du den SVERWEIS weiter wie gewohnt ausfüllen. Gebe als Matrix M30:P49 und als Spaltenindex 4 ein. Als Ergebnis erhältst du “Ware38”.

Die Formel lautet komplett:

=SVERWEIS(BEREICH.VERSCHIEBEN(M29;VERGLEICH(N52;O30:O49;0););M30:P49;4;FALSCH)

Wie im klassischen Fall bist du jetzt sozusagen von der Spalte Rang einen Schritt nach rechts gegangen.

Angenommen, du möchtest jetzt die zu Rang 1 gehörigen Mengen abfragen, müsstest du einen Schritt nach links gehen. Das aber geht mit SVERWEIS nicht.

Verwende deshalb die gleiche Formel wie zuvor, setze als Spaltenindex aber die 2 ein.

=SVERWEIS(BEREICH.VERSCHIEBEN(M29;VERGLEICH(N52;O30:O49;0););M30:P49;2;FALSCH)

Die zu Rang 1 gehörige Menge ist 7.321. Der Wert befindet sich tatsächlich links der Rang-Spalte, SVERWEIS ist nach links gegangen.

Quellen:

[1] http://www.excelformeln.de/formeln.html?welcher=24

[2] https://excelnova.org/2012/10/der-sverweis-nach-links/

]3] https://chandoo.org/wp/2012/09/06/formula-forensics-no-028/