INDEX und MATCH mit mehreren Kriterien

Um Werte mit INDEX und MATCH unter Verwendung mehrerer Kriterien zu suchen, können Sie eine Matrixformel verwenden. Im gezeigten Beispiel lautet die Formel in H8:

{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}

Hinweis: Dies ist ein Array-Formel , und muss mit Strg + Umschalt + Eingabetaste eingegeben werden, außer in Excel 365 .

Dies ist eine fortschrittlichere Formel. Für Grundlagen siehe So verwenden Sie INDEX und MATCH .

Excel-Zählung, wie oft ein Wert in einer Spalte angezeigt wird

Normalerweise ist 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 Verkettung von Werten in a Helferspalte , oder in der Formel selbst, gibt es keine Möglichkeit, mehr als ein Kriterium anzugeben.

Diese Formel umgeht diese Einschränkung durch die Verwendung von Boolesche Logik eine erstellen Array aus Einsen und Nullen, um Zeilen darzustellen, die alle 3 Kriterien erfüllen, und verwenden Sie dann MATCH, um die erste gefundene 1 abzugleichen. Das temporäre Array aus Einsen und Nullen wird mit diesem Snippet generiert:

 
{= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

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 anfängliche Ergebnis sind drei Arrays von TRUE/FALSE-Ergebnissen wie folgt:

 
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

Spitze: Verwenden Sie F9, um diese Ergebnisse zu sehen . Wählen Sie einfach einen Ausdruck in der Bearbeitungsleiste aus und drücken Sie F9.

Die mathematische Operation (Multiplikation) transformiert die TRUE FALSE-Werte in 1s und 0s:

Countdown von einem Datum zum anderen

 
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

Nach der Multiplikation haben wir ein einzelnes Array wie folgt:

 
{1110001}*{0010010}*{1010001}

die als Lookup-Array in die MATCH-Funktion eingespeist wird, mit einem Lookup-Wert von 1:

 
{0010000}

An dieser Stelle ist die Formel eine Standard-INDEX-MATCH-Formel. Die MATCH-Funktion gibt 3 zu INDEX zurück:

 
 MATCH (1,{0010000})

und INDEX gibt ein Endergebnis von 17,00 USD 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 entsteht durch Multiplikation der drei Spalten miteinander. Es ist das Array, das an MATCH übergeben wird.

Nicht-Array-Version

Es ist möglich, dieser Formel einen weiteren INDEX hinzuzufügen, um die Eingabe als Matrixformel mit Strg + Umschalt + Eingabe zu vermeiden:

 
= INDEX (E5:E11,3)

Die INDEX-Funktion kann Arrays nativ verarbeiten, daher wird der zweite INDEX nur hinzugefügt, um das mit der booleschen logischen Operation erstellte Array zu 'fangen' und dasselbe Array wieder an MATCH zurückzugeben. Dazu wird INDEX mit null Zeilen und einer Spalte konfiguriert. Der Nullzeilen-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 Leute, 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 notwendig, Arrayformeln auf besondere Weise einzugeben.

Zeit in Minuten in Excel umrechnen

INDEX und MATCH mit mehreren Kriterien.xlsx