SVERWEIS sucht in einer einzigen Spalte. Oft muss ein Wert aber mehrere Bedingungen erfüllen: der Bestand eines Artikels in einem bestimmten Lager und einer bestimmten Lagerart, der Preis für Kunde und Jahr, die Stunden für Mitarbeiter und Projekt. Mit der richtigen Formel ist das kein Problem.
Die Kurzlösung
Stehen Artikelnummer, Lagerort und Lagerart in B7:D17, der Bestand in E7:E17 und die Suchbegriffe in H6:H8:
=XVERWEIS(1;(B7:B17=H6)*(C7:C17=H7)*(D7:D17=H8);E7:E17;"nicht gefunden")
Für 4712, Lager B, Aussen liefert die Formel den Bestand 500. XVERWEIS gibt es in Excel 2021 und Microsoft 365, für ältere Versionen folgt weiter unten eine Lösung mit INDEX/VERGLEICH.

So arbeitet die Formel
Jeder Vergleich wie (B7:B17=H6) liefert für jede Zeile WAHR oder FALSCH. Werden die Vergleiche multipliziert, rechnet Excel WAHR als 1 und FALSCH als 0. Nur in Zeilen, in denen alle drei Bedingungen zutreffen, ergibt sich 1:
| Art.-Nr. = 4712 | Lagerort = Lager B | Lagerart = Aussen | Produkt |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 1 | 1 | 0 | 0 |
| 1 | 1 | 1 | 1 |
| 0 | 1 | 1 | 0 |
XVERWEIS sucht in diesem Ergebnis die erste 1 und gibt den Bestand aus derselben Zeile zurück. Weitere Bedingungen hängen Sie einfach mit * an.
Für ältere Excel-Versionen
Dasselbe Prinzip funktioniert mit INDEX und VERGLEICH:
=INDEX(E7:E17;VERGLEICH(1;(B7:B17=H6)*(C7:C17=H7)*(D7:D17=H8);0))
In Excel 2019 und älter schließen Sie die Formel mit Strg+Umschalt+Eingabe ab, dann zeigt Excel sie in geschweiften Klammern. In Microsoft 365 genügt Eingabe.
Ist das Ergebnis eine Zahl, die Sie summieren dürfen, geht es auch ganz ohne Matrixformel:
=SUMMEWENNS(E7:E17;B7:B17;H6;C7:C17;H7;D7:D17;H8)
Wenn eine Kombination mehrfach vorkommt
XVERWEIS und INDEX/VERGLEICH liefern den ersten Treffer, SUMMEWENNS die Summe aller Treffer. Ob eine Kombination mehrfach vorkommt, zeigt ZÄHLENWENNS mit denselben Bedingungen. Alle Treffer listet FILTER auf (Excel 2021 und Microsoft 365):
=FILTER(E7:E17;(B7:B17=H6)*(C7:C17=H7)*(D7:D17=H8);"keiner")
In der Beispieldatei steht 4711, Lager A, Innen zweimal in der Liste, mit 100 und 150:
| Formel | Ergebnis |
|---|---|
| XVERWEIS, INDEX/VERGLEICH | 100 (erster Treffer) |
| SUMMEWENNS | 250 |
| ZÄHLENWENNS | 2 |
| FILTER | 100 und 150 |
Varianten und Vergleich
| Lösung | Wann geeignet |
|---|---|
| XVERWEIS mit multiplizierten Bedingungen | Standard in Microsoft 365, Meldung bei fehlendem Wert eingebaut |
| INDEX/VERGLEICH mit multiplizierten Bedingungen | alle Versionen, auch für Text als Ergebnis |
| SUMMEWENNS | Zahlen, alle Versionen, keine Matrixformel |
| FILTER | alle Treffer anzeigen |
| Verkettung mit Trennzeichen | ältere Dateien, siehe unten |
Typische Fehler
- SUMMENPRODUKT mit ZEILE: Eine verbreitete Formel ist
=INDEX(D:D;SUMMENPRODUKT((A1:A20=H5)*(B1:B20=H6)*(C1:C20=H7)*ZEILE(1:20))). Sie funktioniert nur, solange es genau einen Treffer gibt. Bei zwei Treffern addiert sie die Zeilennummern und zeigt einen falschen Wert, etwa 0 aus einer leeren Zeile. Ohne Treffer gibt sie die ganze Spalte D zurück, in Microsoft 365 füllt sie dann die Zellen darunter oder zeigt #ÜBERLAUF!. Verwenden Sie stattdessen die Formeln oben. - Verketten ohne Trennzeichen: Die ältere Lösung
VERGLEICH(H5&H6&H7;A1:A20&B1:B20&C1:C20;0)kann falsche Treffer finden, denn „12“ und „3“ ergeben verkettet dasselbe wie „1“ und „23“. Setzen Sie ein Trennzeichen dazwischen:H6&"|"&H7&"|"&H8undB7:B17&"|"&C7:C17&"|"&D7:D17. - Zahl gegen Text: Steht die Artikelnummer in der Liste als Text und im Suchfeld als Zahl, ist
B7:B17=H6überall FALSCH. Prüfen Sie mit=ISTTEXT(B7). - Unterschiedlich große Bereiche: Alle Bereiche einer Formel müssen dieselben Zeilen umfassen, sonst erscheint #WERT!.
Beispieldatei
Die Datei enthält die Bestandsliste, drei Suchfelder und alle Varianten nebeneinander, einschließlich der doppelten Kombination. Die Datei enthält keine Makros.
Passende Beiträge
Lernen oder machen lassen?
Excel-Formeln, die im Alltag sitzen.
In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.
