SVERWEIS mit mehreren Suchkriterien: so geht es

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.

Beispieldatei mit Bestandsliste, Suchkriterien und allen Varianten

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. = 4712Lagerort = Lager BLagerart = AussenProdukt
0000
1100
1111
0110

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:

FormelErgebnis
XVERWEIS, INDEX/VERGLEICH100 (erster Treffer)
SUMMEWENNS250
ZÄHLENWENNS2
FILTER100 und 150

Varianten und Vergleich

LösungWann geeignet
XVERWEIS mit multiplizierten BedingungenStandard in Microsoft 365, Meldung bei fehlendem Wert eingebaut
INDEX/VERGLEICH mit multiplizierten Bedingungenalle Versionen, auch für Text als Ergebnis
SUMMEWENNSZahlen, alle Versionen, keine Matrixformel
FILTERalle 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&"|"&H8 und B7: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.