Du bist an die Grenzen der SVERWEIS-Funktion gestoßen? Eigentlich hast du 2 oder mehrere Suchkriterien, für die Werte du aus einer Tabelle finden möchtest?
Ich zeige dir in diesem Beitrag Schritt für Schritt was du tun musst, um dieses Problem zu lösen.
SVERWEIS ist eine der praktischsten Formeln aus Excel. Ich selbst verwende sie sehr oft im Berufsleben. Mit dem SVERWEIS() kannst du einen gesuchten Wert aus einer Tabelle finden. Sehen wir uns ein einfaches Beispiel an, um die Formel zu verstehen.
In diesem Beispiel haben wir in einer Tabelle die Umsatzzahlen für jeden Monat. Hier könntest du noch selbst einen gesuchten Wert ablesen. Schwieriger wird es aber wenn du mehrere Hundert Werte in der Tabelle stehen hast. Ich verwende deswegen dieses einfache Beispiel. Eine übliche Fragestellung könnte jetzt lauten:
Wie hoch war der Umsatz im Monat 11?
Um das ganz automatisiert in Excel abzufragen verwendest du die Funktion SVERWEIS()
Mit
Für das Beispiel bedeutet die Formel „übersetzt“:
Dieses Bild verdeutlicht dir nochmal, wie Excel die Suchspalten nummeriert. Achte darauf, dass das Suchkriterium immer in Spalte 1 stehen muss. Die gesuchten zugehörigen Werte müssen immer rechts davon stehen. Wenn deine Daten das nicht zulassen, musst du den SVERWEIS mit einer Kombination aus INDEX und VERGLEICH umgehen.
Meist gibt es in der Realität jedoch für mehrere Merkmale einen bestimmten Wert. Ich habe daher das Datumsbeispiel um Jahre erweitert. Würdest du jetzt mit dem normalen SVERWEIS arbeiten, dann erhälst du noch den richtigen Wert für den gesuchten Umsatz für Monat 11 in Jahr 2019, aber für den Monat 1 im Jahr 2020 erhälst du den falschen Wert aus 2019.
Daher benötigst du einen kleinen Trick, um zwei Suchbedingungen mit dem SVERWEIS zu kombinieren. Das geht mit der Funktion WAHL(). Die erweiterte SVERWEIS-Funktion sieht dann wie folgt aus:
Mit
Die vollständige Funktion für das Beispiel zum Herauskopieren lautet damit:
Mit der Verknüpfung der beiden Suchparameter erreichst du, dass wirklich der richtige zugehörige Wert zu Monat und Jahr gefunden wird. Nimm diese Verknüpfung mit dem „&“-Zeichen vor.
Als nächstes würde in der regulären SVERWEIS-Formel die Matrix festgelegt werden. Das erfolgt jetzt mit der WAHL()-Funktion. Zunächst definierst du, dass es zwei Suchkriterien (Monat in Spalte 1 bzw. A und Jahr in Spalte 2 bzw. B) gibt. Dazu ist es wichtig, dass du in geschweiften Klammern {1.2} in die WAHL-Funktion einträgst.
Schritt 1: WAHL({1.2};
Als nächstes definierst du die Matrix für das erste Suchkriterium. In dem Beispiel ist das ganz einfach Spalte A. Es geht aber auch eine Matrix wie A2 bis A25.
Schritt 2: WAHL({1.2};A:A
Jetzt musst du die Matrix für das zweite Suchkriterium hier auch mit dem „&“-Zeichen verknüpfen. In dem Beispiel ist das ganz einfach Spalte B. Es geht aber auch eine Matrix wie B2 bis B25.
Schritt 3: WAHL({1.2};A:A&B:B;
Zum Schluss fügst du in die WAHL()-Formel noch die Matrix mit dem gesuchten Werten ein. In dem Beispiel ist das ganz einfach Spalte C. Es geht aber auch eine Matrix wie C2 bis C25.
Schritt 4: WAHL({1.2};A:A&B:B;C:C)
Diese Formel WAHL({1.2};A:A&B:B;C:C) fügst du nun anstelle der Matrixparameter in die reguläre SVERWEIS-Fomel ein. Das nachfolgende Bild verdeutlicht noch einmal die Funktionsweise. Mit der „1“ in {1.2} definierst du die Matrix (Spalte A) des ersten Suchkriteriums, mit der „2“ folglich die Matrix (Spalte B) des zweiten Suchkriteriums.
Eine kleine Sache musst du noch beachten. Mit „Monat“, „Jahr“ und „Umsatz“ sind es drei Spalten mit Werten. Für die SVERWEIS-Funktion sind es aber aufgrund der WAHL-Funktion nur zwei Spalten. Du musst du vorstellen, dass die Spalten „Monat“ und „Jahr“ aufgrund der Verknüpfung nur als eine Spalte zählen.
Daher musst du in der SVERWEIS-Funktion als Ausgabespalte auch eine „2“ eintragen. Das nachfolgende Bild verdeutlicht dir nochmal den Zusammenhang.
Das Wichtigste zum Schluss:
Solltest du einmal mehrere Suchparameter haben gibt es auch dafür eine Lösung. Ich habe deshalb das Beispiel noch um Mitarbeiter erweitert.
Das einzige, was du jetzt tun musst, ist die bestehende Formel um ein weiteres Suchkriterium zu erweitern. Das hat auch Einfluss auf den Teil mit der WAHL-Funktion. Die SVERWEIS-Funktion für mehrere Suchkriterien sieht dann wie folgt aus:
Mit
Die vollständige Funktion für das Beispiel mit mehreren Suchkriterien zum Herauskopieren lautet damit:
Mit der Verknüpfung der drei Suchparameter erreichst du, dass wirklich der richtige zugehörige Wert zu Monat, Jahr und Mitarbeiter gefunden wird. Nimm diese Verknüpfung mit dem „&“-Zeichen vor.
Auch hier musst du beachten, dass es mit „Monat“, „Jahr“, „Mitarbeiter“ und „Umsatz“ gleich vier Spalten mit Werten gibt. Für die SVERWEIS-Funktion sind es aber aufgrund der WAHL-Funktion nur zwei Spalten. Du musst du vorstellen, dass die Spalten „Monat“, „Jahr“ und „Mitarbeiter“ aufgrund der Verknüpfung nur als eine Spalte zählen.
Daher musst du in der SVERWEIS-Funktion als Ausgabespalte auch eine „2“ eintragen. Das nachfolgende Bild verdeutlicht dir nochmal den Zusammenhang.
Auch hier die wichtige Anmerkung: Damit die Formel korrekt funktioniert, muss du sie mit STRG + SHIFT + ENTER unter Windows ausführen. Diese Tastenkombination ist nötig, um eine Matrixformel (Arrayformel) zu erstellen. Auf einem Mac sollte sie auch ganz normal mit ENTER funktionieren.
Tipp: Excel wird sehr langsam bei drei Suchkriterien oder bei langen Listen. Versuche daher möglichst bei einem oder zwei Suchkriterien zu bleiben.
Damit du alles besser nachvollziehen kannst, stelle ich dir die Excel Beispieldatei zur Verfügung. In der Excel sind drei Sheets enthalten: