Das Suchkriterium in der Excel-Funktion SVERWEIS

Die Funktion SVERWEIS ist allgemein sehr bekannt und wird durch Excel-User vielfach für Abfragen genutzt.

Ich wurde schon einige Male gefragt, ob das Argument „Suchkriterium“ nur über einen identisch in der Matrix vorkommenden Wert bzw. als Bezug darauf oder auch über andere Funktionen bestimmt werden kann.

Der Beitrag zeigt Dir Möglichkeiten auf. Inwieweit diese praktisch für Dich relevant sein können, wirst Du selbst entscheiden. Hier war nur wichtig zu zeigen, wie es gehen kann.

1. Der klassische SVERWEIS

Zur Erinnerung, hier noch einmal das klassische Vorgehen. Zur Erinnerung, die Syntax sieht so aus:

SVERWEIS(Suchkriterium;Matrix;Spaltenindex;[Bereich_Verweis])

Dabei gelten:

Suchkriterium ist das Kriterium, wonach in einer Matrix gesucht wird.

Matrix ist der Bereich, worin nah dem Suchkriterium gesucht wird.

Spaltenindex bezeichnet die Spalte, aus der der Rückgabewert kommen soll.

Bereich_Verweis gibt an, ob das gesuchte Ergebnis nur in etwa mit dem Suchbegriff übereinstimmen (WAHR) oder komplett übereinstimmen soll (FALSCH).

Gegeben sei eine Liste mit 12 Monaten und dazugehörigen Umsätzen.

In Zelle F6 wurde zur einfachen Auswahl mit der Datenüberprüfung eine Liste mit den Monaten aus dem Bereich B7:B18 hinterlegt.

Zelle F8 enthält die Formel

            =SVERWEIS($F$6;B7:C18;2)

Das Argument Bereich_Verweis ist optional und bleibt hier außen vor.

Alternativ kannst Du auch

            =SVERWEIS(3;B7:B18;2)

schreiben. Nur diese Formel wäre nicht dynamisch, da das Suchkriterium fest eingegeben wurde.

2. Ersatz des Suchkriteriums durch eine Excel-Funktion

2.1 Ersatz durch die Funktion VERGLEICH

In Deiner Umsatzliste werden die Monate als Zahlen von 1 bis 12 dargestellt. Die mit der Datenüberprüfung in Zelle F6 hinterlegte Liste verwendet allerdings die abgekürzten Monatsbezeichnungen Jan bis Dez.

Um trotzdem mit dem SVERWEIS arbeiten zu können, muss z.B. „Mrz“ in eine Zahl übersetzt werden.

Dazu bietet sich die Funktion VERGLEICH an. Lege Dir über Formeln / Namen definieren zuerst eine Liste „Monate“ an. Im Beitrag erfolgte die Auflistung im Bereich B21:B32. Lege die Datenüberprüfung für F6 entsprechend an.

Rufe nun in F6 Deinen gesuchten Monat auf.

Verwende in F6 diese Formel:

=SVERWEIS(VERGLEICH($F$6;Monate;0);B7:C18;2)

Excel findet mit dieser Formel nun den 3. Monat, den „Mrz“ und gibt in F8 das richtige Ergebnis aus.

2.2 Ersatz durch die Funktion SVERWEIS

In dieser Variante lässt Du zunächst z.B. das Suchkriterium „Mai“ in eine Zahl „übersetzen“. Dazu brauchst Du eine Matrix.

In F8 schließlich schreibst Du diese Formel:

=SVERWEIS(SVERWEIS($F$6;B21:C32;2;FALSCH);B7:C18;2;FALSCH)

Mit dem Formelteil SVERWEIS($F$6;B21:C32;2;FALSCH) als Ersatz für das Suchkriterium suchst Du den Monat „Mai“ aus F6 in der „Übersetzungsmatrix“ (s.o.). Aus der Spalte 2 dieser Matrix erhältst Du den Rückgabewert „5“. Allerdings musst Du hier das Argument „Bereich_Verweis“ mit FALSCH benennen, denn Du suchst genau den Rückgabewert zu „Mrz“.

Mit dem Rückgabewert 5 als Suchkriterium kannst Du nun den klassischen SVERWEIS durchführen und den Mai-Umsatz suchen. Hier gibst Du ebenfalls noch FALSCH als Argument ein.

2.3 Ersatz durch die Funktion INDEX

Mit der Funktion INDEX als Ersatz für das Suchkriterium gehst Du ähnlich vor.

Unter Nutzung der Funktion VERGLEICH suchst Du wieder nach der Position z.B. des Monats „Sep“ aus F6 im Bereich B21:C32 und entnimmst mit der INDEX-Funktion in der Spalte 2 den Rückgabewert 9.

Dann lässt Du mit SVERWEIS den Umsatz zurückgeben.

= SVERWEIS(INDEX(B21:C32;VERGLEICH($F$6;Monate;0);

2);B7:C18;2;FALSCH)

3. Fazit

Du hast gesehen, dass das Suchkriterium auf vielfache Weise mit anderen Funktionen umschrieben werden kann und trotzdem zum richtigen Ergebnis führt.

Bleibt die Frage, warum Du das so kompliziert machen solltest?

Am besten bleibst Du bei der klassischen Variante, wie unter Abschn. 1 beschrieben.

Es kann aber durchaus vorkommen, dass Zuarbeiten von anderen Mitarbeitern nicht so ideal, wie Du es brauchst, erfolgen.

Immerhin gibt es die Möglichkeit, es dann etwas komplizierter zu machen.