Generische Formel
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Zusammenfassung
Um Werte mit INDEX und MATCH anhand mehrerer Kriterien zu suchen, können Sie eine Array-Formel verwenden. In dem gezeigten Beispiel lautet die Formel in H8:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365.
Erläuterung
Dies ist eine fortgeschrittenere Formel. Grundlagen finden Sie unter Verwendung von INDEX und MATCH.
Normalerweise wird eine INDEX MATCH-Formel so konfiguriert, dass MATCH so eingestellt ist, dass sie einen einspaltigen Bereich durchsucht und eine Übereinstimmung basierend auf bestimmten Kriterien liefert. Ohne die Verkettung von Werten in einer Hilfsspalte oder in der Formel selbst können nicht mehr als ein Kriterium angegeben werden.
Diese Formel umgeht diese Einschränkung, indem sie mithilfe der Booleschen Logik ein Array von Einsen und Nullen erstellt, um Zeilen darzustellen, die allen drei Kriterien entsprechen, und dann MATCH verwendet, um die erste gefundene 1 abzugleichen. Das temporäre Array von Einsen und Nullen wird mit diesem Snippet generiert:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Hier vergleichen wir den Artikel in H5 mit allen Artikeln, die Größe in H6 mit allen Größen und die Farbe in H7 mit allen Farben. Das erste Ergebnis sind drei Arrays von TRUE / FALSE-Ergebnissen wie folgt:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tipp: Verwenden Sie F9, um diese Ergebnisse anzuzeigen. Wählen Sie einfach einen Ausdruck in der Formelleiste aus und drücken Sie F9.
Die mathematische Operation (Multiplikation) transformiert die TRUE FALSE-Werte in 1s und 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Nach der Multiplikation haben wir ein einzelnes Array wie folgt:
(0;0;1;0;0;0;0)
Dies wird als Lookup-Array mit einem Lookup-Wert von 1 in die MATCH-Funktion eingespeist:
MATCH(1,(0;0;1;0;0;0;0))
Zu diesem Zeitpunkt ist die Formel eine Standard-INDEX-MATCH-Formel. Die MATCH-Funktion gibt 3 an INDEX zurück:
=INDEX(E5:E11,3)
und INDEX gibt ein Endergebnis von 17,00 USD zurück.
Array-Visualisierung
Die oben erläuterten Arrays können schwierig zu visualisieren sein. Das Bild unten zeigt die Grundidee. Die Spalten B, C und D entsprechen den Daten im Beispiel. Spalte F wird durch Multiplizieren der drei Spalten erstellt. Es ist das Array, das an MATCH übergeben wird.
Nicht-Array-Version
Es ist möglich, dieser Formel einen weiteren INDEX hinzuzufügen, ohne dass eine Array-Formel mit Strg + Umschalt + Eingabe eingegeben werden muss:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Die INDEX-Funktion kann Arrays nativ verarbeiten, sodass der zweite INDEX nur hinzugefügt wird, um das mit der booleschen Logikoperation erstellte Array zu "fangen" und dasselbe Array erneut an MATCH zurückzugeben. Zu diesem Zweck wird INDEX mit null Zeilen und einer Spalte konfiguriert. Der Null-Zeilen-Trick bewirkt, dass INDEX Spalte 1 aus dem Array zurückgibt (das ohnehin schon eine Spalte ist).
Warum sollten Sie die Nicht-Array-Version wollen? Manchmal vergessen Benutzer, eine Array-Formel mit Strg + Umschalt + Eingabe einzugeben, und die Formel gibt ein falsches Ergebnis zurück. Eine Nicht-Array-Formel ist also "kugelsicherer". Der Kompromiss ist jedoch eine komplexere Formel.
Hinweis: In Excel 365 ist es nicht erforderlich, Array-Formeln auf besondere Weise einzugeben.
