INDEX und ÜBEREINSTIMMUNG mit mehreren Kriterien

Dies ist eine erweiterte Formel. Grundlagen finden Sie unter Verwendung von INDEX und MATCH.

Normalerweise wird eine Indexübereinstimmungsformel mit Übereinstimmungssatz konfiguriert, um einen einspaltigen Bereich zu durchsuchen und eine Übereinstimmung basierend auf bestimmten Kriterien bereitzustellen. Ohne Verkettung von Werten in einer Hilfsspalte oder in der Formel selbst gibt es keine Möglichkeit, mehr als ein Kriterium anzugeben.,

Diese Formel umgeht diese Einschränkung, indem Sie mithilfe der booleschen Logik ein Array von Einsen und Nullen erstellt, um Zeilen darzustellen, die allen 3 Kriterien entsprechen, und dann MATCH verwendet, um die ersten 1 gefundenen übereinzustimmen. 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 das Element in H5 mit allen Elementen, die Größe in H6 mit allen Größen und die Farbe in H7 mit allen Farben. Das ursprüngliche Ergebnis sind drei Arrays mit ECHTEN / FALSCHEN 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 WAHREN FALSCHEN 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 dieses:
{0;0;1;0;0;0;0}

das als Lookup-Array mit einem Lookup-Wert von 1:
MATCH(1,{0;0;1;0;0;0;0})

Zu diesem Zeitpunkt ist die Formel eine Standard-Indexübereinstimmungsformel., Die MATCH-Funktion gibt 3 an INDEX zurück:
=INDEX(E5:E11,3)

und INDEX gibt ein Endergebnis von $17.00 zurück.
Array-Visualisierung
Die oben erläuterten Arrays können schwer zu visualisieren sein. Das Bild unten zeigt die Grundidee. Die Spalten B, C und D entsprechen den Daten im Beispiel. Spalte F wird durch die Multiplikation der drei Spalten zusammen. Es ist das array übergeben zu ENTSPRECHEN.,
 
Nicht-Array-Version
Es ist möglich, dieser Formel einen weiteren INDEX hinzuzufügen, sodass die Eingabe als Array-Formel mit Strg + Umschalt + Eingabetaste vermieden werden muss:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))

Die Indexfunktion kann Arrays nativ verarbeiten, sodass der zweite INDEX nur hinzugefügt wird, um das mit dem Array erstellte Array boolesche Logik-Operation und gibt das gleiche Array wieder zu entsprechen. Dazu wird der INDEX mit null Zeilen und einer Spalte konfiguriert. Der Null-Zeilen-Trick bewirkt, dass INDEX Spalte 1 aus dem Array zurückgibt (was ohnehin schon eine Spalte ist).,
Warum möchten Sie die Nicht-Array-Version? Manchmal vergessen die Leute, eine Array-Formel mit Strg + Umschalt + Eingabetaste einzugeben, und die Formel gibt ein falsches Ergebnis zurück. Eine Nicht-Array-Formel ist also „kugelsicher“. Der Kompromiss ist jedoch eine komplexere Formel.
Hinweis: In Excel 365 ist es nicht erforderlich, Array-Formeln auf besondere Weise einzugeben.