SVERWEIS mit 2 Suchkriterien
Du möchtest in Excel einen bestimmten Wert aus einer Tabelle heraussuchen? Und dabei hast du mehr als ein Kriterium, wie z. B. Kunde und Produkt? Hier und im Video erklären wir dir, wie du den SVERWEIS mit 2 Suchkriterien einsetzen kannst.
Inhaltsübersicht
SVERWEIS mit 2 Suchkriterien einfach erklärt
Den klassischen SVERWEIS in Excel nutzt du, um einen bestimmten Wert aus einer Tabelle herauszusuchen oder um Tabellen zusammenzufassen. Er durchsucht Tabellen immer senkrecht und hat dabei nichts mit Verweisen zu tun, sondern ist eher eine Suchfunktion.
Aufgebaut ist sie so: =SVERWEIS(Kriterium; Bereich; Spaltennummer; 1/0).
Die klassische Funktion kommt allerdings schnell an ihre Grenzen, da sie nach nur einem Kriterium suchen kann. Brauchst du in deinem Anwendungsfall aber zwei Bedingungen, benötigst du einen kleinen Trick, den SVERWEIS mit 2 Kriterien:
=SVERWEIS(Suchkriterium1&Suchkriterium2; WAHL(); Spaltennummer; 0)
Die WAHL-Funktion brauchst du zusätzlich, um zwei Bereiche in der Funktion definieren zu können.
Schau dir das direkt am Beispiel an. Hier bestellen Kunden mehrfach pro Jahr eine bestimmte Produktkategorie.
Im Beispiel suchst du nach dem Kunden „Müller“. Dieser hat mehrfach verschiedene Produkte bestellt. Um herauszufinden, für wie viel Geld er Smartphones eingekauft hat benötigst du den SVERWEIS mit zwei Bedingungen.
=SVERWEIS(„Müller“&„Smartphone“;WAHL({1.2};A2:A10&B2:B10;C2:C10);2;0)
Dann kannst du den Kunden („Müller“) und ein bestimmtes Produkt („Smartphone“) eingeben. Kunde Müller hat also in Quartal 1 Smartphones im Wert von 1.702 € gekauft.
Schauen wir uns nun die Details in der Schreibweise an.
SVERWEIS mit 2 Suchkriterien — Syntax
Wie ist ein doppelter SVERWEIS aufgebaut?
=SVERWEIS(„Müller“&„Smartphone“;WAHL({1.2};A2:A10&B2:B10;C2:C10);2;0)
- Name der Funktion: =SVERWEIS( )
- Kriterien, nach denen gesucht wird. Die Suchkriterien werden mit & verbunden → „Müller“&„Smartphone“
-
Der Bereich wird ersetzt durch die WAHL-Funktion. Mit ihrer Hilfe können zwei Suchbereiche angegeben werden: WAHL({1.2};A2:A10&B2:B10;C2:C10)
- Schritt: Spaltenangabe der zwei Bereiche in {Spalte1.Spalte2} → {1.2};
- Schritt: Bereich Suchkriterium 1 (ganze Spalte oder festgelegter Bereich) → A2:A10
- Schritt: Verknüpfung mit & Bereich Suchkriterium 2 → &B2:B10;
- Schritt: Bereich des gesuchten Werts → C2:C10
- Spaltennummer des gesuchten Werts: Durch die WAHL-Funktion werden die Spalte A und B sozusagen zu einer verknüpft, deshalb ist Spalte C erst Nummer zwei: 2
- Übereinstimmung: Du suchst du eine exakte Übereinstimmung mit dem Kriterium, schreibe deshalb am Ende: 0
Wichtig: Beim Excel SVERWEIS mit mehreren Suchkriterien handelt es sich um eine leistungsfähigere Matrixformel. Schließe die Eingabe der Formel unter Windows unbedingt mit [Strg] + [Shift] + [Enter] ab, um sie korrekt zu erstellen. Bei Mac genügt ein Enter.
SVERWEIS mit 2 Kriterien — Beispiel
Im nächsten Beispiel hast du Angaben zum Jahr, zum Monat und zur Absatzmenge. Du nutzt wieder den Excel SVERWEIS mit mehreren Suchkriterien, um die Absatzmenge aus dem Oktober 2022 zu erhalten.
Die Funktion sieht so aus: =SVERWEIS(F6&F7;WAHL({1.2};A:A&B:B;C:C);2;0)
Die Suchkriterien wurden diesmal mit Zellbezügen (F6&F7) angegeben. An der WAHL-Funktion ändert sich nichts, da die Kriterien wieder in Spalte 1 und 2 stehen. Nur die Bereiche der Kriterien wurden jetzt in ganzen Spalten angegeben, also die ganze Spalte A und die ganze Spalte B.
Übrigens: Ist dein Excel nicht auf Deutsch eingestellt, musst du im Englischen ein Komma in der WAHL-Funktion {1,2} verwenden und in der Schweiz ein Backslash {1\2}.
Da Spalte 1 und 2 durch die Verknüpfung mit & als eine Spalte zählen, steht der gesuchte Wert wieder in Spalte 2. Die 0 am Ende steht für die gesuchte exakte Übereinstimmung mit der Jahres- und Monatsangabe. Denke daran, die Formeleingabe unter Windows wieder mit [Strg] + [Shift] + [Enter] abzuschließen. Im Beispiel hat das Unternehmen im Oktober 2022 605 Stück seiner Produkte abgesetzt.
Die Funktion kannst du auch mit mehreren Bedingungen anwenden. Dafür erweiterst du einfach die Suchkriterien mit & (&G9). Außerdem ergänzt du die Angabe in der WAHL-Funktion. Das könnte dann so aussehen:
=SVERWEIS(G7&G8&G9;WAHL({1.2.4};A:A&B:B&D:D;C:C);2;0)
Im Beispiel hat Mitarbeiter B im Oktober 2022 605 Teile abgesetzt.
Problem: Gesuchter Wert steht nicht direkt neben Suchkriterien
Die Funktion SVERWEIS mit zwei Kriterien funktioniert so allerdings nur, wenn der gesuchte Wert in der Spalte direkt neben den Suchkriterien steht. Ist es nicht möglich, die Daten so anzuordnen, kannst du auch den SVERWEIS mit WENN-Funktion verwenden.
Im Beispiel oben sieht die Formel so aus:
=SVERWEIS(F6;WENN(B:B=F7;A:C;„“);3;0)
Achte darauf:
- Vorne steht nur ein Kriterium (F6, also das gesuchte Jahr), nicht beide wie beim SVERWEIS mit WAHL-Funktion.
- Dafür enthält die WENN-Funktion das zweite Kriterium. Die WENN-Funktion enthält eine Bedingung (B:B=F7) und einen Dann-Wert (A:C, also der gesamte Bereich). Dieser wird ausgegeben, wenn die Bedingung wahr ist. Andernfalls wird ein Sonst-Wert („“) ausgegeben.
- Nur wenn die Prüfung (B:B=F7 → In Spalte B steht eine 10) wahr ist, wird der Bereich (A:C) ausgegeben. Dieser Bereich vervollständigt den SVERWEIS, sodass er funktioniert.
- Der Sonst-Wert „“ tritt ein, wenn die Spalte B das zweite Kriterium nicht enthält. Das steht für eine leere Zelle und es entsteht eine Fehlermeldung.
- Die Spaltenangabe ist 3, hier werden nämlich keine Spalten verknüpft. Du zählst einfach bis zur Spalte mit dem gesuchten Wert.
- 0 sucht unverändert nach einer exakten Übereinstimmung mit Jahr und Monat.
Tipp: Einfacher ist die Verwendung der neueren XVERWEIS-Funktion.
SVERWEIS mit 2 Kriterien — Häufigste Fragen
-
Wie funktioniert SVERWEIS mit 2 Suchkriterien?
Der SVERWEIS mit 2 Kriterien ist so aufgebaut: =SVERWEIS(Suchkriterium1&Suchkriterium2; WAHL({Spalte1.Spalte2};Bereich1&Bereich2;Suchbereich);Spaltennummer;0). Statt des einfachen Bereichs wird die WAHL-Funktion benutzt.
-
Wie kann ich den SVERWEIS mit WENN-Funktion einsetzen?
Den SVERWEIS mit 2 Kriterien kannst du auch mit der WENN-Funktion aufbauen. Das sieht beispielsweise so aus: =SVERWEIS(„Müller“;WENN(B1:B10=“Smartphone“;A1:F10;““);3;0). Diese Funktion kannst du einsetzen, wenn die gesuchten Daten nicht direkt neben den Kriterien stehen.
Excel INDIREKT
Wie der Excel SVERWEIS mit mehreren Spalten funktioniert, weißt du jetzt. Eine weitere nützliche Funktion mit vielen Einsatzmöglichkeiten rund um Zellbezüge ist INDIREKT . Schau dir direkt an, wofür du die Funktion alles nutzen kannst.
