Ja, er ist einfach nicht totzukriegen: Der gleichermaßen geliebte wie gehasste SVERWEIS! Wer schon eine Weile mit Excel arbeitet, kann sich vielleicht noch an das Glücksgefühl erinnern, als man endlich diesen SVERWEIS verstanden hat. Auch heute leistet er noch gute Dienste, auch wenn in neuen Excel-Versionen mit der XVERWEIS-Funktion ein mächtiger Nachfolger zur Verfügung steht.
Neben einigen strukturellen Einschränkungen hat der SVERWEIS aber einen großen Nachteil:
Es lässt sich immer nur ein Suchkriterium verwenden. Was aber ist, wenn das nicht reicht und man eigentlich zwei Kriterien bräuchte? Oder drei?
Geht nicht?
Geht doch! Und zwar mit dem „Super“-SVERWEIS, den ich dir in diesem Artikel vorstellen möchte.
Beispieldatei herunterladen
Der „normale“ SVERWEIS
Für diejenigen unter meinen Lesern, die mit dem SVERWEIS nicht ganz so vertraut sind, kommt hier zuerst eine kurze Übersicht über den Zweck und die Anwendung dieser Funktion.
Der SVERWEIS hilft dir, in einer Tabelle nach einem bestimmten Wert zu suchen und dann einen entsprechenden Wert aus einer anderen Spalte derselben Zeile zurückzugeben. Man könnte beispielsweise in einer Tabelle nach einem bestimmten Artikel suchen und dann den Lagerbestand für diesen Artikel ausgeben, der in einer Spalte daneben steht.
Der SVERWEIS hat vier Argumente:
- Suchkriterium: Der Wert, den du suchen möchtest (z.B. der Artikel).
- Matrix: Die Tabelle, die du durchsuchen möchtest. Die Suchspalte muss dabei an erster Stelle stehen.
- Spaltenindex: Die Spalte, aus der der entsprechende Wert zurückgegeben werden soll (z.B. der Bestand).
- [Bereich_Verweis]: Ein optionales Argument, das angibt, ob du eine ungefähre oder genaue Übereinstimmung möchtest. FALSCH steht für eine genaue Übereinstimmung und WAHR für eine ungefähre.
Für das Beispiel mit den Artikelbeständen könnte das wie im folgenden Bild aussehen. In Zelle B2 gibt man den gewünschten Artikel ein, in der Zelle daneben findet der SVERWEIS den entsprechenden Bestand aus der Artikeltabelle:
=SVERWEIS(B2;B6:C9;2;FALSCH)
Da der Bestand in der zweiten Spalte der Tabelle steht, wird im dritten Argument der Wert 2 angegeben.
Der „normale“ SVERWEIS
Der vierte Parameter (hier: FALSCH) ist zwar optional, aber extrem wichtig. In geschätzten 99% der Fälle gibt man hier FALSCH an, um nur exakte Übereinstimmungen zu berücksichtigen. Du solltest daher diesen Parameter immer angeben, auch wenn er optional ist. Denn lässt man ihn weg, nimmt Excel vereinfacht gesagt den nächstbesten Treffer. Und das ist in den allermeisten Fällen nicht gewünscht.
Ändere ich jetzt den Artikel in B2, wird sofort der entsprechende Bestand geliefert. Ist der Artikel jedoch in der Tabelle nicht vorhanden, liefert SVERWEIS einen #NV-Fehler:
Nicht vorhandene Werte führen zu einem #NV-Fehler
Soweit, so gut.
Etwas kniffeliger wird es, wenn man zwei Suchkriterien hat. In meiner erweiterten Artikeltabelle gibt es auch eine Spalte für die Größe, die bei der Suche berücksichtigt werden muss:
Wie lassen sich zwei Suchkriterien umsetzen
Wie lässt sich diese Aufgabe lösen?
Option 1: Die Hilfsspalte
Da man im SVERWEIS nur ein Suchkriterium angeben kann, wäre eine Option, mit einer Hilfsspalte zu arbeiten. So könnte ich die beiden Zellen mit Artikel und Größe in jeweils einer Zelle zusammenfassen:
=G2&H2
Und die gleiche Hilfsspalte richte ich in meiner Artikeltabelle ein, bevor ich den SVERWEIS anwenden kann:
=SVERWEIS(F2;F6:I17;4;FALSCH)
Lösung mit einer Hilfsspalte
Wichtig:
Die Tabellenbezüge ändern sich jetzt natürlich, da die Hilfsspalte mit berücksichtigt werden muss. Und somit befinden sich die Bestände dann in der vierten Spalte, daher der Wert 4 im dritten Parameter. Auf diese Weise erhalte ich mein gewünschtes Ergebnis, abhängig vom Artikel in G2 und der Größe in H2.
Aber Hilfsspalten sind halt nicht sonderlich elegant und blähen die Ursprungstabelle unnötig auf. Mit Hilfe eines kleinen Tricks können wir auf dieses unschöne Hilfskonstrukt verzichten und stattdessen den Super-SVERWEIS erstellen.
Option 2: Der Super-SVERWEIS
Dazu kombinieren wir den SVERWEIS mit der WAHL-Funktion und erhalten (Trommelwirbel!) folgendes Ergebnis:
=SVERWEIS(G2&H2;WAHL({1.2};G6:G17&H6:H17;I6:I17);2;FALSCH)
Super-SVERWEIS: Lösung ohne Hilfsspalte
Wichtig:
Wenn du nicht Microsoft 365 im Einsatz hast, dann musst du diese Formel als Array-Formel eingeben. Das heißt, sie muss zwingend mit Strg+Umschalt+Eingabe abgeschlossen werden, sonst funktioniert es nicht. Anschließend sieht man in der Bearbeitungszeile, dass die Formel von geschweiften Klammern umgeben ist (Microsoft 365-Anwender brauchen das jedoch nicht).
Wie funktioniert nun diese „magische“ Formel?
Zerlegen wir das gute Stück in seine Bestandteile. Die beiden Suchkriterien werden – ähnlich, wie oben in Option 1 beschrieben – zu einem einzigen zusammengefasst. Aber eben ohne Hilfsspalte, sondern direkt in der Formel. Somit lautet der erste Parameter G2&H2.
Für den zweiten Parameter (im Bild blau markiert), der ja die zu durchsuchende Tabelle definiert, kommt die WAHL-Funktion zum Einsatz.
Das Ende der Verschachtelungen: Du hast die WAHL
Zurück zu unserem Anwendungsbeispiel:
WAHL({1.2};G6:G17&H6:H17;I6:I17)
Allgemein gesprochen wählt man mit dieser Funktion aus einer Liste von Werten mit Hilfe eines Index einen bestimmten Wert aus:
=WAHL(Index; Wert1; Wert2; ...)
In meinem Beispiel habe ich für den Index die Werte 1 und 2 in geschweiften Klammern und getrennt durch einen Punkt angegeben. Was bedeutet das?
In Excel-Funktionen lassen sich Wertelisten entweder als Bezug (beispielsweise in der Form A1:A5) eingeben, oder direkt als konstanten Werte, getrennt durch ein Semikolon: Somit sind =SUMME(A1:A5) und =SUMME(10;4;5;3) gültige Eingaben.
Wenn es sich dabei um eine Matrix handelt, werden die Konstanten in geschweifte Klammern gesetzt. Spalten werden dabei durch einen Punkt getrennt, Zeilen durch ein Semikolon. So lässt sich die Tabelle auf der linken Seite wie im folgenden Bild auch in einer Kurzschreibweise darstellen:
Beispiel für eine Matrix-Schreibweise
Das bringt uns zurück zu unserer WAHL-Funktion: Die Angabe von {1.2} im ersten Parameter bedeutet also, dass wir an zwei Spalten interessiert sind.
Der nächste Parameter der WAHL-Funktion fasst die Spalte Artikel und Größe zusammen (G6:G17&H6:H17), im letzten Parameter kommt dann die Spalte mit den Beständen (I6:I17).
Ich weiß, das ist ein sehr abstraktes Konstrukt. Etwas deutlicher wird es vielleicht, wenn man nur den Teil mit der WAHL-Funktion in der Bearbeitungsleiste markiert und dann die Funktionstaste F9 drückt (danach unbedingt wieder mit ESC abbrechen, sonst werden die Werte fix in die Formel übernommen):
Die WAHL-Funktion wurde mit F9 temporär aufgelöst
Hier sieht man die Wertepaare, die in den geschweiften Klammern ermittelt wurden:
- „HoodieM“.68
- „HoddieL“.56
- „HoddieXL“.67
- usw.
Also immer der Artikel mit der Größe kombiniert und dann durch einen Punkt getrennt der Bestand. Das ist also die „virtuelle“ Tabelle, die an den SVERWEIS zum Durchsuchen übergeben wird.
Der Rest der SVERWEIS-Funktion ist dann wieder bekannter Stoff: Da unsere virtuelle Tabelle nur aus zwei Spalten besteht, wird als dritter Parameter der Wert 2 angegeben. Und es wird wieder nach genauer Übereinstimmung gesucht („FALSCH“).
Wichtig ist nur, dass diese Formel in der Nicht-Microsoft-365-Version unbedingt als Matrix-Funktion eingeben wird, also die Eingabe mit STRG+Umschalt+Eingabe abgeschlossen wird.
Das Spiel lässt sich nun (fast) beliebig weitertreiben. Falls du also 3 oder mehr Kriterien benötigst, musst du in der Formel nur die entsprechenden Spalten kombinieren:
SVERWEIS mit drei Suchkriterien
Wie man sieht, steckt im alten SVERWEIS mehr drin, als man denkt. Und damit habe ich dich für heute genug strapaziert.
Viel Spaß beim Ausprobieren!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.