N-ten Treffer mit INDEX abrufen

Um den n-ten Treffer eines Werts aus einem Bereich zu finden und die zugehörigen Daten abzurufen, können Sie eine Formel verwenden, die auf den INDEX-, ZEILEN--, KKLEINST- und WENN-Funktionen basiert.

Wie ruft man Informationen ab, die dem n-ten Treffer eines Werts mithilfe von INDEX zugeordnet sind?

Um die Informationen zum 3. Treffer von Usain Bolt in der oben dargestellten Tabelle zu erhalten, kombinieren Sie die ZEILE- und WENN-Funktionen, um ein Array zu erstellen, das die Zeilennummern aller Einträge zu Usain Bolt enthält. Anschließend nutzen Sie die KKLEINST-Funktion, um die Zeilennummer des gewünschten n-ten Treffers zu ermitteln. Übergeben Sie dieses Ergebnis schließlich an die INDEX-Funktion, um die entsprechenden Informationen aus derselben Zeile abzurufen.

Allgemeine Syntax

=INDEX()return_range,SMALL(IF())lookup_array=lookup_value,ROW()lookup_array-ROW(INDEX())lookup_array,1,1))+1),n))

√ Hinweis: Dies ist eine Arrayformel, für deren Eingabe Sie Strg+Umschalttaste+Eingabetastedrücken müssen.

Um die Informationen zum 3. Treffer von Usain Boltzu erhalten, kopieren oder geben Sie die folgenden Formeln in die Zellen G8 und G9 ein und drücken Sie Strg+Umschalttaste+Eingabetaste, um die Ergebnisse zu erhalten:

Jahr (Zelle G8)
=INDEX()B6:B12;KKLEINST(WENN())C6:C12=G5;ZEILE()C6:C12-ZEILE(INDEX())C6:C12,1,1))+1);G6))
Zeit (Zelle G9)
=INDEX()D6:D12;KKLEINST(WENN())C6:C12=G5;ZEILE()C6:C12-ZEILE(INDEX())C6:C12,1,1))+1);G6))

Erläuterung der Formel

Hier verwenden wir die folgende Formel als Beispiel:

=INDEX(B6:B12,)SMALL()IF(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))

• ZEILE(C6:C12): Die ZEILE-Funktion gibt für jede Zelle im Bereich C6:C12 deren jeweilige Zeilennummer in einem Array wie folgt zurück: {6;7;8;9;10;11;12}.
• ZEILE(INDEX(C6:C12;1,1)): Die Funktionen INDEX und ZEILE geben die erste Zeilennummer des Suchbereichs C6:C12 in diesem Excel-Arbeitsblatt zurück – nämlich 6.
• WENN(C6:C12=G5;)ZEILE(C6:C12)-ZEILE(INDEX(C6:C12;1,1))+1)=WENN(C6:C12=G5;){6;7;8;9;10;11;12}-6+1):Die WENN-Funktion prüft, ob jeder Wert im Bereich C6:C12 mit dem Wert in Zelle G5, Usain Bolt, übereinstimmt. Trifft dies zu, liefert die Funktion das Ergebnis „entsprechende Zeilennummer – 6 + 1“; andernfalls gibt sie FALSCH zurück. Das Ergebnis liegt daher als Array wie folgt vor: {FALSCH;2;3;4;FALSCH;FALSCH;FALSCH}.
• KKLEINST()WENN(C6:C12=G5;)ZEILE(C6:C12)-ZEILE(INDEX(C6:C12;1,1))+1);G6)=KKLEINST(){FALSCH;2;3;4;FALSCH;FALSCH;FALSCH};G6):Der Ausschnitt gibt den 3.kleinsten Wert (Wert in Zelle)G6) aus dem Array zurück – nämlich 4.
• INDEX(B6:B12;)KKLEINST()WENN(C6:C12=G5;)ZEILE(C6:C12)-ZEILE(INDEX(C6:C12;1,1))+1);G6)) = INDEX(B6:B12;4)Die INDEX-Funktion gibt den 4. Wert im Bereich B6:B12 zurück – nämlich Peking 2008.

Verwandte Funktionen

Verwandte Formeln