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.

4 Kommentare zu „Das Suchkriterium in der Excel-Funktion SVERWEIS“

  1. Ich nutze seit langem nur noch die Kombination von INDEX () und Vergleich() und verzichte ganz auf SVERWEIS() oder VERWEIS().

    z.B. :
    INDEX(Matrix;VERGLEICH();VERGLEICH())
    INDEX(B21:C32; VERGLEICH($F$6;Monate;0); VERGLEICH(“Umsatz“;Titel_H;0) )

    Analog zur Definition des Namens „Monate“ legt man sich vorher noch für den Bereich des horizontalen Titels in B6:C6 einen Namen „Titel_H“ an.

    Wenn man die Vorgehensweise mal verinnerlicht hat, muss man sich anstelle von 3 Funktionen nur noch 1 merken.

    Gruß Hermann

  2. Hallo Hermann,
    ich stimme Dir voll und ganz zu, bin selbst Fan der Index-Funktion.
    Aber es gibt Viele, die SVERWEIS lieben. Für die habe ich den Beitrag geschrieben.
    Danke für Deinen Kommentar.
    Viele Grüße
    Gerhard

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s