=XVERWEIS(1;(A2:A7=E2)*(B2:B7=F2);C2:C7) liefert den Preis aus der Zeile, in der das Produkt E2 und die Größe F2 ist. Jeder Vergleich prüft jede Zeile, ihre Multiplikation ergibt nur dort 1, wo beide zutreffen, und XVERWEIS (englisch XLOOKUP) sucht diese 1. Die Funktion braucht Excel 2021 oder Microsoft 365; die Version mit INDEX und VERGLEICH weiter unten funktioniert in jeder Version. Die Tabelle zeigt die englische Schreibweise, =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7); du kannst die Formeln dort aber auch deutsch eingeben, mit Semikolons.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=XVERWEIS(1;(A2:A7=E2)*(B2:B7=F2);C2:C7)Tea und Large treffen sich in Zeile 5, also liefert G2 $3.00. Wähle Juice und Small: wieder $3.00, aus einer anderen Zeile. Gib ein viertes Argument für den Fall an, dass keine Zeile auf beides passt: =XVERWEIS(1;(A2:A7=E2)*(B2:B7=F2);C2:C7;"No such item").
So funktionieren die multiplizierten Bedingungen
A2:A7=E2 vergleicht jedes Produkt mit E2 und liefert sechs Werte WAHR oder FALSCH. Multiplizierst du zwei solche Listen, wird aus WAHR eine 1 und aus FALSCH eine 0, und eine Zeile ist nur dann 1, wenn sie in beiden 1 ist. Spalte D zeigt diese Liste, übergelaufen aus einer einzigen Formel.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
=(A2:A7=F2)*(B2:B7=G2)Nur D5 ist 1. Ändere F2 oder G2, und die 1 wandert. Jedes weitere Kriterium ist ein weiteres *(Bereich=Wert), und die Bedingungen müssen keine Gleichheit sein: *(C2:C7<3) ergänzt "Preis unter 3". Jeder Bereich muss dieselben Zeilen abdecken (A2:A7, B2:B7, C2:C7): Hat der Rückgabebereich eine andere Größe als die Bedingungen, liefert XVERWEIS #WERT! (englisch #VALUE!; die Tabellen hier zeigen die englischen Fehlernamen).
INDEX und VERGLEICH mit mehreren Kriterien
Für Excel 2019 und älter kann VERGLEICH (englisch MATCH) dieselbe Matrix nach der 1 durchsuchen, und INDEX liefert den Preis an dieser Position.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=INDEX(C2:C7;VERGLEICH(1;(A2:A7=E2)*(B2:B7=F2);0))Coffee und Large ist Position 2 der Matrix, und INDEX liefert $3.50. In Excel 2019 und älter ist das eine Matrixformel: Drück Strg+Umschalt+Eingabe (Cmd+Umschalt+Return auf dem Mac) statt nur der Eingabetaste, und Excel zeigt sie in geschweiften Klammern. Mit der einfachen Eingabetaste liefert sie dort meist #NV oder #WERT!. In Excel 365 reicht die Eingabetaste. Die Form mit einem Kriterium steht auf der Seite INDEX und VERGLEICH.
Die Kriterien zu einem Schlüssel verbinden
Der andere Weg macht aus zwei Kriterien eines, indem er sie verbindet. SVERWEIS (englisch VLOOKUP) braucht die verbundenen Werte in einer Hilfsspalte am Anfang der Tabelle (die Seite SVERWEIS zeigt diese Version). XVERWEIS kann die Bereiche in der Formel verbinden, also braucht es keine Hilfsspalte.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=XVERWEIS(E2&"|"&F2;A2:A7&"|"&B2:B7;C2:C7)A2:A7&"|"&B2:B7 baut sechs Schlüssel wie Juice|Large, und XVERWEIS findet Juice|Large darunter: $4.00. Setz ein Trennzeichen zwischen die Teile. Ohne eines ergeben "AB" und "C" dasselbe "ABC" wie "A" und "BC", und die Suche kann die falsche Zeile liefern.
Ist der gesuchte Wert eine Zahl, und kommt jede Kombination einmal vor, liefert SUMMEWENNS (englisch SUMIFS) dieselbe Antwort ganz ohne Matrix: =SUMMEWENNS(C2:C7;A2:A7;E2;B2:B7;F2). Die Funktion liefert 0 statt eines Fehlers, wenn nichts passt, und das kann einen Tippfehler verstecken.
Alle Treffer mit FILTER zurückgeben
XVERWEIS und INDEX/VERGLEICH liefern die erste passende Zeile. Passen mehrere Zeilen, und willst du alle, nimmst du FILTER mit denselben Bedingungen.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
=FILTER(C2:D8;(A2:A8="North")*(B2:B8="Phone"))Drei Zeilen sind North und Phone, also lässt F2 ihre Quartale und Umsätze nach F2:G4 überlaufen. Ändere A3 in South, und die Liste schrumpft auf zwei. Passt keine Zeile, liefert FILTER #KALK! (englisch #CALC!); ein drittes Argument wie "None" zeigt stattdessen Text. In deutschem Excel heißt FILTER genauso: =FILTER(C2:D8;(A2:A8="North")*(B2:B8="Phone")). Weitere Optionen stehen auf der Seite FILTER.
Übung: drei Kriterien
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
Du bist dran: Gib in G4 den Umsatz für die Region in G1, das Produkt in G2 und das Quartal in G3 zurück.
Häufig gestellte Fragen
Wie verwende ich XVERWEIS mit mehreren Kriterien?
Multipliziere einen Vergleich pro Kriterium und suche die 1: =XVERWEIS(1;(A2:A7=E2)*(B2:B7=F2);C2:C7). Jeder Vergleich ergibt pro Zeile WAHR oder FALSCH, das Produkt ist nur dort 1, wo alle WAHR sind, und XVERWEIS liefert die erste solche Zeile.
Wie mache ich INDEX und VERGLEICH mit zwei Kriterien?
Nimm dieselben multiplizierten Bedingungen in VERGLEICH: =INDEX(C2:C7;VERGLEICH(1;(A2:A7=E2)*(B2:B7=F2);0)). In Excel 2019 und älter bestätigst du die Formel mit Strg+Umschalt+Eingabe (Cmd+Umschalt+Return auf dem Mac).
Kann SVERWEIS zwei Kriterien verwenden?
Nicht direkt. Füg am Anfang der Tabelle eine Hilfsspalte ein, die die beiden Werte verbindet, etwa =A2&"|"&B2, und such dann den verbundenen Wert: =SVERWEIS(E2&"|"&F2;Hilfstabelle;Spalte;FALSCH).
Kann SUMMEWENNS eine Suche mit zwei Kriterien ersetzen?
Ja, wenn der Wert eine Zahl ist und jede Kombination einmal vorkommt: =SUMMEWENNS(C2:C7;A2:A7;E2;B2:B7;F2). Die Funktion liefert 0 statt #NV, wenn keine Zeile passt, und addiert die Werte, wenn eine Kombination zweimal vorkommt.
Wie suche ich mit ODER-Kriterien?
Addiere die Bedingungen, statt sie zu multiplizieren: (A2:A7="Tea")+(A2:A7="Juice") ist 1 oder mehr, wo eine davon zutrifft. Such dann einen Wert größer als 0, zum Beispiel mit =XVERWEIS(WAHR;((A2:A7="Tea")+(A2:A7="Juice"))>0;C2:C7).
