Mehrkriteriensuche mit INDEX und VERGLEICH

Bei der Arbeit mit einer großen Datenbank in einer Excel-Tabelle mit mehreren Spalten und Zeilenbeschriftungen ist es immer knifflig, etwas zu finden, das mehreren Kriterien entspricht. In diesem Fall können Sie eine Array-Formel mit den Funktionen INDEX und VERGLEICH verwenden.

Wie führt man eine Suche mit mehreren Kriterien durch?

Um das Produkt zu finden, das weiß, mittelgroß und mit einem Preis von 18 $ ist, wie im obigen Bild gezeigt, können Sie die boolesche Logik nutzen, um ein Array von 1en und 0en zu generieren, das die Zeilen anzeigt, die den Kriterien entsprechen. Die VERGLEICH-Funktion wird dann die Position der ersten Zeile finden, die allen Kriterien entspricht. Danach findet INDEX die entsprechende Produktnummer in derselben Zeile.

Allgemeine Syntax

=INDEX(Rückgabebereich;VERGLEICH(1;(Kriteriumswert1=Kriterienbereich1*Kriteriumswert2=Kriterienbereich2*(…);0))

√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Enter eingeben müssen.

Um das Produkt zu finden, das weiß, mittelgroß und mit einem Preis von 18 $ ist, kopieren Sie bitte die folgende Formel in die Zelle H8 oder geben Sie sie ein, und drücken Sie Strg + Umschalt + Enter, um das Ergebnis zu erhalten:

=INDEX(B5:B10;VERGLEICH(1;("Weiß"=C5:C10)*("Mittel"=D5:D10)*(18=E5:E10);0))

Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu gestalten:

=INDEX(B5:B10;VERGLEICH(1;(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10);0))

Erklärung der Formel

=INDEX(B5:B10;VERGLEICH(1;(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10);0))

• (H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Die Formel vergleicht die Farbe in der Zelle H3 mit allen Farben im Bereich C5:C10; vergleicht die Größe in H5 mit allen Größen in D5:D10; vergleicht den Preis in H6 mit allen Preisen in E5:E10. Das anfängliche Ergebnis sieht so aus:
  {WAHR;FALSCH;WAHR;FALSCH;WAHR;FALSCH}*{FALSCH;FALSCH;WAHR;WAHR;WAHR;FALSCH}*{FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH}.
  Die Multiplikation wird WAHR und FALSCH in 1en und 0en umwandeln:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  Nach der Multiplikation erhalten wir ein einzelnes Array wie dieses:
  {0;0;0;0;1;0}.
• VERGLEICH(1;(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10);0) = VERGLEICH(1;{0;0;0;0;1;0};0): Der Vergleichstyp 0 fordert die VERGLEICH-Funktion auf, eine genaue Übereinstimmung zu finden. Die Funktion gibt dann die Position von 1 im Array {0;0;0;0;1;0} zurück, die 5 ist.
• INDEX(B5:B10B5:B10;VERGLEICH(1;(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10);0)) = INDEX(B5:B10B5:B10;5): Die INDEX-Funktion gibt den 5. Wert im Produktnummer-Bereich B5:B10 zurück, welcher 30005 ist.

Verwandte Funktionen

Verwandte Formeln