Mehrfachkriteriensuche mit INDEX und VERGLEICH

Bei der Arbeit mit einer großen Datenbank in einem Excel-Arbeitsblatt mit zahlreichen Spalten und Zeilenüberschriften ist es oft schwierig, Einträge zu finden, die mehrere Kriterien erfüllen. In einem solchen Fall können Sie eine Arrayformel mit den INDEX-und VERGLEICH-Funktionenverwenden.

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

Um das Produkt zu finden, das weiß und mittelgroß ist und einen Preis von $18 hat (siehe Abbildung oben), können Sie die boolesche Logik nutzen, um ein Array aus 1en und 0en zu erstellen, das die Zeilen anzeigt, die alle Kriterien erfüllen. Anschließend ermittelt die VERGLEICH-Funktion die Position der ersten Zeile, die alle Kriterien erfüllt, und INDEX liefert dann die entsprechende Produkt-ID aus derselben Zeile.

Allgemeine Syntax

=INDEX()return_range,MATCH(1,())criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

√ Hinweis: Dies ist eine Arrayformel, die mit Strg+Umschalttaste+Eingabetasteeingegeben werden muss.

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

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

Alternativ können Sie Eine Zellreferenz verwenden, 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,MATCH(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 Zelle H3 mit allen Farben im Bereich C5:C10, die Größe in H5 mit allen Größen in D5:D10 und den Preis in H6 mit allen Preisen in E5:E10. Das vorläufige Ergebnis sieht wie folgt aus:
  {WAHR;FALSCH;WAHR;FALSCH;WAHR;FALSCH}*{FALSCH;FALSCH;WAHR;WAHR;WAHR;FALSCH}*{FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH}.
  Durch die Multiplikation werden WAHR- und FALSCH-Werte in 1en und 0en umgewandelt:
  {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 folgt:
  {0;0;0;0;1;0}.
• VERGLEICH(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=VERGLEICH(1,) Der Vergleichstyp 0 weist die VERGLEICH-Funktion an, eine exakte Übereinstimmung zu finden. Die Funktion gibt dann die Position der 1 im Array {0;0;0;0;1;0} zurück – nämlich 5.
• INDEX()B5:B10,VERGLEICH(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10 Die INDEX-Funktion gibt den 5. Wert im Bereich der Produkt-IDs B5:B10 zurück – nämlich 30005.

Verwandte Funktionen

Verwandte Formeln