SVERWEIS mit mehreren Kriterien: so geht es mit einer Hilfsspalte
SVERWEIS kann nur nach einem Wert suchen. Mit einer Hilfsspalte, die zwei Merkmale zu einem Schlüssel verbindet, findest du trotzdem zum Beispiel den Umsatz von Berlin im zweiten Quartal.
Kurz gesagt: Bau in Spalte A einen Schlüssel aus beiden Kriterien, =B2&C2, und such mit einem genauso gebauten Suchwert darin: =SVERWEIS(E2&F2;A2:D9;4;FALSCH). Die Hilfsspalte muss ganz links in der Matrix stehen.
Das Problem: SVERWEIS nimmt den ersten Treffer
In einer Umsatzliste steht jede Filiale mehrmals, einmal pro Quartal. Ein normaler SVERWEIS auf „Berlin“ findet den ersten Berliner Eintrag und hört dort auf. Ob das Q1 oder Q3 ist, hängt nur von der Reihenfolge der Liste ab. Du brauchst also eine Spalte, in der jede Kombination aus Filiale und Quartal nur einmal vorkommt.
Schritt 1: Die Hilfsspalte bauen
Füg links neben den Daten eine Spalte ein und verbinde darin die beiden Kriterien mit dem &-Zeichen. Aus Berlin und Q2 wird BerlinQ2. Diese Verbindung ist eindeutig, auch wenn Berlin und Q2 einzeln mehrfach vorkommen. Probier es aus: Die Formel wird nach unten ausgefüllt.
Aufgabe Jede Artikelnummer gibt es in mehreren Lagern. Damit SVERWEIS später nach Lager und Artikel zugleich suchen kann, brauchst du eine Hilfsspalte. Verbinde in A2 Lager und Artikelnummer ohne Trennzeichen zu einem Schlüssel, also zum Beispiel HamburgA-104. Die Formel wird bis A8 ausgefüllt.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Schlüssel | Lager | Artikelnr. | Bestand |
| 2 | Hamburg | A-104 | 120 | |
| 3 | Berlin | A-101 | 85 | |
| 4 | Hamburg | A-101 | 40 | |
| 5 | Leipzig | A-104 | 64 | |
| 6 | Berlin | A-104 | 15 | |
| 7 | Leipzig | A-102 | 230 | |
| 8 | Hamburg | A-102 | 98 |
Die Hilfsspalte muss links stehen, weil SVERWEIS immer in der ersten Spalte der Matrix sucht. Wenn sie stört, kannst du sie später ausblenden. Die Formeln funktionieren weiter.
Schritt 2: Mit dem verbundenen Suchwert suchen
Der Trick: Auch der Suchwert wird verbunden. E2&F2 ergibt BerlinQ2, und genau diesen Text sucht SVERWEIS in der Hilfsspalte.
Aufgabe Spalte A verbindet schon Filiale und Quartal (=B2&C2). Hol in G2 mit SVERWEIS den Umsatz der Filiale aus E2 im Quartal aus F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Schlüssel | Filiale | Quartal | Umsatz € | Filiale | Quartal | Umsatz € |
| 2 | HamburgQ2 | Hamburg | Q2 | 48200 | Berlin | Q2 | |
| 3 | BerlinQ1 | Berlin | Q1 | 51900 | |||
| 4 | LeipzigQ2 | Leipzig | Q2 | 33700 | |||
| 5 | HamburgQ1 | Hamburg | Q1 | 45100 | |||
| 6 | BerlinQ2 | Berlin | Q2 | 56300 | |||
| 7 | LeipzigQ1 | Leipzig | Q1 | 29800 | |||
| 8 | HamburgQ3 | Hamburg | Q3 | 50600 | |||
| 9 | BerlinQ3 | Berlin | Q3 | 54400 |
| Argument | Bedeutung | Im Beispiel |
|---|---|---|
| Suchkriterium | Beide Kriterien, genauso verbunden wie in der Hilfsspalte | E2&F2 |
| Matrix | Beginnt mit der Hilfsspalte | A2:D9 |
| Spaltenindex | Gezählt ab der Hilfsspalte | 4 (Umsatz) |
| Bereich_Verweis | Genaue Suche, die Liste ist nicht sortiert | FALSCH |
Vergiss FALSCH nicht. Die Schlüssel sind nicht sortiert, und ohne FALSCH sucht SVERWEIS ungefähr. Das liefert dann irgendeinen Wert, ohne Fehlermeldung.
Mit Trennzeichen auf Nummer sicher
Ohne Trennzeichen können zwei verschiedene Kombinationen denselben Schlüssel ergeben. Artikel 12 bei Kunde 34 und Artikel 123 bei Kunde 4 werden beide zu 1234. Ein Bindestrich oder senkrechter Strich dazwischen verhindert das: =B2&"-"&C2. Wichtig ist nur, dass du den Suchwert dann genauso baust: E2&"-"&F2. In der nächsten Übung vergleichst du Lieferantenpreise, und die Hilfsspalte nutzt einen Bindestrich.
Aufgabe Mehrere Lieferanten bieten denselben Artikel zu unterschiedlichen Preisen an. Die Hilfsspalte A verbindet Artikelnummer und Lieferant mit einem Bindestrich (=B2&"-"&C2). Hol in G2 mit SVERWEIS den Preis für den Artikel aus E2 beim Lieferanten aus F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Schlüssel | Artikelnr. | Lieferant | Preis € | Artikelnr. | Lieferant | Preis € |
| 2 | 4712-Weber KG | 4712 | Weber KG | 18,4 | 4711 | Weber KG | |
| 3 | 4711-Becker AG | 4711 | Becker AG | 12,9 | |||
| 4 | 4715-Schmidt GmbH | 4715 | Schmidt GmbH | 7,8 | |||
| 5 | 4711-Schmidt GmbH | 4711 | Schmidt GmbH | 11,5 | |||
| 6 | 4712-Becker AG | 4712 | Becker AG | 17,9 | |||
| 7 | 4715-Weber KG | 4715 | Weber KG | 8,2 | |||
| 8 | 4711-Weber KG | 4711 | Weber KG | 12,4 |
Mehr als zwei Kriterien gehen genauso: =B2&"-"&C2&"-"&D2 in der Hilfsspalte und E2&"-"&F2&"-"&G2 als Suchwert.
Ohne Hilfsspalte: XVERWEIS und INDEX/VERGLEICH
Mit XVERWEIS brauchst du keine Hilfsspalte. Du verbindest einfach die ganzen Spalten in der Formel: =XVERWEIS(E2&F2;B2:B9&C2:C9;D2:D9). Eine zweite Variante prüft beide Bedingungen einzeln und multipliziert sie: =XVERWEIS(1;(B2:B9=E2)*(C2:C9=F2);D2:D9). Wie das genau funktioniert, zeigt die Seite XVERWEIS mit mehreren Kriterien.
Mit INDEX und VERGLEICH klappt das zweite Prinzip ebenfalls: =INDEX(D2:D9;VERGLEICH(1;(B2:B9=E2)*(C2:C9=F2);0)). Daneben kursiert ein älterer Trick, der SVERWEIS mit WAHL eine Matrix im Speicher bauen lässt. Er funktioniert, ist aber schwer zu lesen, deshalb lassen wir ihn hier weg.
Ist das Ergebnis eine Zahl und jede Kombination kommt nur einmal vor, liefert auch SUMMEWENNS den richtigen Wert: =SUMMEWENNS(D2:D9;B2:B9;E2;C2:C9;F2). Für Texte als Ergebnis geht das nicht.
Typische Fehler
#NV, obwohl die Kombination existiert
Fast immer sind Suchwert und Schlüssel unterschiedlich gebaut: andere Reihenfolge (Q2Berlin statt BerlinQ2), Trennzeichen nur auf einer Seite oder ein Leerzeichen zu viel. Schreib den Suchwert testweise in eine freie Zelle, zum Beispiel =E2&F2, und vergleich ihn mit der Hilfsspalte.
Datum in der Verbindung
Verbindest du ein Datum mit &, wird daraus die interne Seriennummer, also eine Zahl wie 46023. Das ist kein Problem, solange Hilfsspalte und Suchwert beide so gebaut sind. Lesbarer wird es mit TEXT, zum Beispiel TEXT(C2;"MM.JJJJ").
Hilfsspalte nicht ganz links
Steht der Schlüssel rechts von den Werten, die du zurückgeben willst, kommt SVERWEIS nicht an sie heran. Leg die Hilfsspalte an den Anfang der Tabelle oder nimm XVERWEIS.
Doppelte Kombinationen
Gibt es dieselbe Kombination zweimal, liefert SVERWEIS still den ersten Treffer. Prüf die Hilfsspalte mit =ZÄHLENWENN(A:A;A2)>1 auf Dubletten.
SVERWEIS mit mehreren Kriterien auf Englisch
| Deutsch | Englisch |
|---|---|
=SVERWEIS(E2&F2;A2:D9;4;FALSCH) | =VLOOKUP(E2&F2,A2:D9,4,FALSE) |
=XVERWEIS(E2&F2;B2:B9&C2:C9;D2:D9) | =XLOOKUP(E2&F2,B2:B9&C2:C9,D2:D9) |
| INDEX, VERGLEICH, WAHL, SUMMEWENNS | INDEX, MATCH, CHOOSE, SUMIFS |
Das &-Zeichen ist in beiden Sprachen gleich. Die Übungen verstehen beide Schreibweisen.
In welchen Excel-Versionen funktioniert das?
Hilfsspalte und SVERWEIS funktionieren in allen Excel-Versionen, auch in Excel 2016, 2019, Excel für Mac und Excel für das Web. Genau das ist der Vorteil dieser Methode. XVERWEIS gibt es nur in Microsoft 365, Excel 2021 und Excel 2024. Die Variante mit INDEX und VERGLEICH rechnet mit ganzen Spalten auf einmal. In Versionen mit dynamischen Arrays (Microsoft 365, Excel 2021 und neuer) reicht dafür Enter, in Excel 2019 und älter musst du sie als ältere Matrixformel mit Strg+Umschalt+Enter abschließen.
Häufige Fragen
Kann SVERWEIS zwei Suchkriterien haben?
Nicht direkt, SVERWEIS hat nur ein Suchkriterium. Über die Hilfsspalte machst du aus zwei Kriterien eines: Schlüssel und Suchwert werden beide mit & verbunden.
Wie viele Kriterien sind möglich?
So viele du willst. Häng einfach weitere Zellen mit & an, in der Hilfsspalte und im Suchwert in derselben Reihenfolge.
Geht SVERWEIS mit mehreren Bedingungen auch ohne Hilfsspalte?
Mit SVERWEIS allein nur über den WAHL-Trick, der schwer zu lesen ist. Einfacher sind XVERWEIS oder INDEX mit VERGLEICH, beide kommen ohne Hilfsspalte aus.
Darf ich die Hilfsspalte ausblenden?
Ja. Ausgeblendete Spalten rechnen ganz normal weiter. Lösch sie nur nicht, sonst stimmt der Spaltenindex nicht mehr und die Formeln liefern #BEZUG! oder falsche Werte.