Excel-Formel: Holen Sie sich die n-te Übereinstimmung mit INDEX / MATCH -

Inhaltsverzeichnis

Generische Formel

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Zusammenfassung

Um mehrere übereinstimmende Werte aus einem Datensatz mit einer Formel abzurufen, können Sie die Funktionen IF und SMALL verwenden, um die Zeilennummer jeder Übereinstimmung zu ermitteln und diesen Wert an INDEX zurückzugeben. In dem gezeigten Beispiel lautet die Formel in I7:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Wo benannte Bereiche sind Amts (D4: D11), ID (I3) und IDs (C4: C11).

Beachten Sie, dass dies eine Array-Formel ist und mit Strg + Umschalt + Eingabetaste eingegeben werden muss.

Erläuterung

Im Kern ist diese Formel einfach eine INDEX-Formel, die den Wert in einem Array an einer bestimmten Position abruft. Der Wert für n wird in Spalte H angegeben, und alle "schwere" Arbeit, die die Formel leistet, besteht darin, die Zeile herauszufinden, aus der ein Wert abgerufen werden soll, wobei die Zeile der "n-ten" Übereinstimmung entspricht.

Die IF-Funktion ermittelt, welche Zeilen eine Übereinstimmung enthalten, und die SMALL-Funktion gibt den n-ten Wert aus dieser Liste zurück. Innerhalb von IF lautet der logische Test:

ids=id

was dieses Array ergibt:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Beachten Sie, dass die Kunden-ID an der 1. und 4. Position übereinstimmt, die als WAHR angezeigt werden. Das Argument "value if true" in IF generiert eine Liste der relativen Zeilennummern mit diesem Ausdruck:

ROW(ids)-ROW(INDEX(ids,1,1))+1

welches dieses Array erzeugt:

(1;2;3;4;5;6;7)

Dieses Array wird dann durch die logischen Testergebnisse "gefiltert", und die IF-Funktion gibt das folgende Array-Ergebnis zurück:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Beachten Sie, dass wir gültige Zeilennummern für Zeile 1 und Zeile 2 haben.

Dieses Array wird dann von SMALL verarbeitet, das so konfiguriert ist, dass Werte in Spalte H verwendet werden, um "n-te" Werte zurückzugeben. Die SMALL-Funktion ignoriert automatisch die logischen Werte TRUE und FALSE im Array. Am Ende reduzieren sich die Formeln auf:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Fehler behandeln

Sobald für eine bestimmte ID keine Übereinstimmungen mehr vorhanden sind, gibt die SMALL-Funktion einen # NUM-Fehler zurück. Sie können diesen Fehler mit der IFERROR-Funktion behandeln oder indem Sie Logik hinzufügen, um Übereinstimmungen zu zählen und die Verarbeitung abzubrechen, sobald die Zahl in Spalte H größer als die Anzahl der Übereinstimmungen ist. Das Beispiel hier zeigt einen Ansatz.