Získejte n-tou shodu pomocí INDEX

Chcete-li najít n-tou shodu hodnoty z rozsahu a získat odpovídající data, můžete použít vzorec založený na INDEX, ŘÁDEK, MALÉ a IF funkce.

Jak získat informace spojené s n-tou shodou hodnoty s INDEXem?

Chcete-li získat informace o 3. zápase Usaina Bolta v tabulce, jak je uvedeno výše, můžete použít funkce ROW a IF společně k získání pole, které se skládá z čísel řádků všech hodnot Usain Bolt. Poté pomocí funkce SAMLL načtěte číslo řádku n-té odpovídající hodnoty, kterou jste zadali. Nakonec vložte výsledek do funkce INDEX, abyste získali odpovídající informace na stejném řádku.

Obecná syntaxe

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

√ Poznámka: Toto je maticový vzorec, který vyžaduje zadání pomocí Ctrl + směna + vstoupit.

Chcete-li získat informace o 3. zápase Usaina Bolta, zkopírujte nebo zadejte níže uvedené vzorce do buněk G8 a G9 a stiskněte Ctrl + směna + vstoupit získat výsledky:

Rok (buňka G8)
= INDEX (B6: B12,MALÁ(KDYŽ(C6: C12=G5,ŘÁDEK(C6: C12-ROW(INDEX(C6: C12,1,1))+1),G6))
Čas (buňka G9)
= INDEX (D6: D12,MALÁ(KDYŽ(C6: C12=G5,ŘÁDEK(C6: C12-ROW(INDEX(C6: C12,1,1))+1),G6))

Vysvětlení vzorce

Zde jako příklad používáme níže uvedený vzorec:

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

• ŘÁDEK(C6:C12): Funkce ROW vrátí čísla řádků všech buněk v rozsahu C6: C12 v poli jako je toto: {6;7;8;9;10;11;12}.
• ŘÁDEK(INDEX(C6:C12,1,1;XNUMX;XNUMX)): Funkce INDEX a ROW vrátí číslo prvního řádku rozsahu vyhledávání C6: C12 v tomto excelovém listu, což je 6.
• IF(C6:C12=G5,ŘÁDEK(C6:C12)-ŘÁDEK(INDEX(C6:C12,1,1;XNUMX;XNUMX))+ 1) = IF(C6:C12=G5,{6;7;8;9;10;11;12}-6+ 1): Funkce KDYŽ kontroluje, zda je každá hodnota v rozsahu C6: C12 odpovídá hodnotě v buňce G5, Usain Šroub. Pokud ano, funkce vrátí výsledek „jeho odpovídající číslo řádku-6+1“; V opačném případě vrátí FALSE. Takže výsledky by byly v poli, jako je toto: {FALSE;2;3;4;FALSE;FALSE;FALSE}.
• MALÝ(IF(C6:C12=G5,ŘÁDEK(C6:C12)-ŘÁDEK(INDEX(C6:C12,1,1;XNUMX;XNUMX))+ 1),G6) = MALÝ({FALSE;2;3;4;FALSE;FALSE;FALSE},G6): Fragment vrátí 3rd (hodnota v buňce G6) nejmenší hodnota z pole, která je 4.
• INDEX(B6:B12;MALÝ(IF(C6:C12=G5,ŘÁDEK(C6:C12)-ŘÁDEK(INDEX(C6:C12,1,1;XNUMX;XNUMX))+ 1),G6)) = INDEX(B6:B12,4) Funkce INDEX vrací 4hodnotu v ročním rozmezí B6: B12, který je Peking 2008.

Související funkce

Související vzorce