N-tes Vorkommen mit INDEX abrufen

Um das n-te Vorkommen eines Werts in einem Bereich zu finden und die entsprechenden Daten abzurufen, können Sie eine Formel verwenden, die auf den Funktionen INDEX, ZEILE, KLEINSTE und WENN basiert.

Wie ruft man Informationen ab, die dem n-ten Vorkommen eines Werts mit INDEX zugeordnet sind?

Um die Informationen über das 3. Vorkommen von Usain Bolt in der obigen Tabelle zu erhalten, können Sie die Funktionen ZEILE und WENN kombinieren, um ein Array zu erstellen, das aus den Zeilennummern aller Usain Bolt-Werte besteht. Verwenden Sie dann die KLEINSTE-Funktion, um die Zeilennummer des spezifizierten n-ten übereinstimmenden Werts abzurufen. Schließlich füttern Sie das Ergebnis in die INDEX-Funktion, um die entsprechenden Informationen in derselben Zeile zu erhalten.

Allgemeine Syntax

=INDEX(Rückgabebereich;KLEINSTE(WENN(Suchbereich=Suchwert;ZEILE(Suchbereich-ZEILE(INDEX(Suchbereich;1;1))+1);n))

√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Enter eingeben müssen.

Um die Informationen über das 3. Vorkommen von Usain Bolt zu erhalten, kopieren Sie bitte die folgenden Formeln in die Zellen G8 und G9, und drücken Sie Strg + Umschalt + Enter, um die Ergebnisse zu erhalten:

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

Erklärung der Formel

Hier verwenden wir die folgende Formel als Beispiel:

=INDEX(B6:B12;KLEINSTE(WENN(C6:C12=G5;ZEILE(C6:C12)-ZEILE(INDEX(C6:C12;1;1))+1);G6))

• ZEILE(C6:C12): Die ZEILE-Funktion gibt die Zeilennummern jeder Zellen im Bereich C6:C12 in einem Array wie folgt zurück: {6;7;8;9;10;11;12}.
• ZEILE(INDEX(C6:C12;1;1)): Die INDEX- und ZEILE-Funktionen geben die erste Zeilennummer des Suchbereichs C6:C12 in diesem Excel-Arbeitsblatt zurück, die 6 ist.
• 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 überprüft, ob jeder Wert im Bereich C6:C12 mit dem Wert in der Zelle G5, Usain Bolt, übereinstimmt. Wenn ja, gibt die Funktion das Ergebnis „seine entsprechende Zeilennummer-6+1“ zurück; andernfalls gibt sie FALSCH zurück. Die Ergebnisse wären in einem Array wie folgt: {FALSCH;2;3;4;FALSCH;FALSCH;FALSCH}.
• KLEINSTE(WENN(C6:C12=G5;ZEILE(C6:C12)-ZEILE(INDEX(C6:C12;1;1))+1);G6) = KLEINSTE({FALSCH;2;3;4;FALSCH;FALSCH;FALSCH};G6): Der Ausdruck gibt den 3. (Wert in Zelle G6) kleinsten Wert aus dem Array zurück, der 4 ist.
• INDEX(B6:B12;KLEINSTE(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 Jahresbereich B6:B12 zurück, der Peking 2008 ist.

Verwandte Funktionen

Verwandte Formeln