Preluați a n-a potrivire cu INDEX

Pentru a găsi a n-a potrivire a unei valori dintr-un interval și pentru a prelua datele corespunzătoare, puteți utiliza o formulă bazată pe INDEX, RÂND, MIC și IF funcții.

Cum se recuperează informațiile asociate cu a n-a potrivire a unei valori cu INDEX?

Pentru a obține informații despre al 3-lea meci al lui Usain Bolt în tabelul prezentat mai sus, puteți utiliza funcțiile ROW și IF împreună pentru a obține o matrice care constă din numerele de rând ale tuturor valorilor Usain Bolt. Apoi utilizați funcția SAMLL pentru a prelua numărul rândului celei de-a n-a valori potrivite pe care ați specificat-o. În cele din urmă, transmiteți rezultatul funcției INDEX pentru a obține informațiile corespunzătoare pe același rând.

Sintaxa generică

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

√ Notă: Aceasta este o formulă matrice care necesită să introduceți cu Ctrl + Schimba + Intrați.

Pentru a obține informații despre al 3-lea meci al lui Usain Bolt, copiați sau introduceți formulele de mai jos în celulele G8 și G9 și apăsați Ctrl + Schimba + Intrați pentru a obține rezultatele:

An (celula G8)
= INDEX (B6: B12,MIC(DACA(C6: C12=G5,RÂND(C6: C12-RÂND(INDEX(C6: C12,1,1))+1),G6))
Ora (celula G9)
= INDEX (D6: D12,MIC(DACA(C6: C12=G5,RÂND(C6: C12-RÂND(INDEX(C6: C12,1,1))+1),G6))

Explicația formulei

Aici folosim formula de mai jos ca exemplu:

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

• RÂND(C6:C12): Funcția ROW returnează numerele de rând ale fiecărei celule din interval C6: C12 într-o matrice ca aceasta: {6;7;8;9;10;11;12}.
• RÂND(INDEX(C6:C12,1,1)): Funcțiile INDEX și ROW returnează primul număr de rând al intervalului de căutare C6: C12 în această foaie de lucru Excel, care este 6.
• DACA(C6:C12=G5,RÂND(C6:C12)-RÂND(INDEX(C6:C12,1,1))+ 1) = DACA(C6:C12=G5,{6;7;8;9;10;11;12}-6+ 1): Funcția IF verifică dacă fiecare valoare din interval C6: C12 se potrivește cu valoarea din celulă G5, usain Bolt. Dacă da, funcția va returna rezultatul „rândului corespunzător numărul-6+1”; În caz contrar, returnați un FALSE. Deci rezultatele ar fi într-o matrice ca aceasta: {FALS;2;3;4;FALS;FALSE;FALSE}.
• MIC(DACA(C6:C12=G5,RÂND(C6:C12)-RÂND(INDEX(C6:C12,1,1))+ 1),G6) = MIC({FALS;2;3;4;FALS;FALSE;FALSE},G6): Fragmentul returnează 3rd (valoarea în celulă G6) cea mai mică valoare din matrice, adică 4.
• INDEX(B6:B12,MIC(DACA(C6:C12=G5,RÂND(C6:C12)-RÂND(INDEX(C6:C12,1,1))+ 1),G6)) = INDEX(B6:B12,4) Funcția INDEX returnează fișierul 4a-a valoare din intervalul de ani B6: B12, Care este Beijing 2008.

Funcții conexe

Formule conexe