Haal de nde match op met INDEX

Om de zoveelste overeenkomst van een waarde uit een bereik te vinden en de bijbehorende gegevens op te halen, kunt u een formule gebruiken die is gebaseerd op de INDEX, RIJ, KLEIN, en IF functies.

Hoe informatie ophalen die is gekoppeld aan de n-de overeenkomst van een waarde met INDEX?

Om het te krijgen informatie over de 3e wedstrijd van Usain Bolt in de tabel zoals hierboven weergegeven, kunt u de ROW- en IF-functies samen gebruiken om een ​​array te krijgen die bestaat uit de rijnummers van alle Usain Bolt-waarden. Gebruik vervolgens de SAMLL-functie om het rijnummer op te halen van de n-de overeenkomende waarde die u hebt opgegeven. Voer ten slotte het resultaat in de INDEX-functie in om de overeenkomstige informatie in dezelfde rij te krijgen.

Algemene syntaxis

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

√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter.

Om het te krijgen informatie over de 3e wedstrijd van Usain Bolt, kopieer of voer de onderstaande formules in de cellen G8 en G9 in en druk op Ctrl + Shift + Enter om de resultaten te krijgen:

Jaar (cel G8)
=INDEX(B6: B12,KLEIN(ALS(C6: C12=G5,RIJ(C6: C12-RIJ(INDEX(C6: C12,1,1))+1),G6))
Tijd (cel G9)
=INDEX(D6: D12,KLEIN(ALS(C6: C12=G5,RIJ(C6: C12-RIJ(INDEX(C6: C12,1,1))+1),G6))

Verklaring van de formule

Hier gebruiken we de onderstaande formule als voorbeeld:

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

• RIJ (C6:C12): De RIJ-functie retourneert de rijnummers van elke cel in het bereik C6: C12 in een array als deze: {6;7;8;9;10;11;12}.
• RIJ(INDEX(C6:C12,1,1)): De INDEX- en ROW-functies retourneren het eerste rijnummer van het opzoekbereik C6: C12 in dit Excel-werkblad, dat is 6.
• ALS(C6:C12=G5,RIJ (C6:C12)-RIJ(INDEX(C6:C12,1,1))+ 1) = ALS(C6:C12=G5,{6;7;8;9;10;11;12}-6+ 1): De ALS-functie controleert of elke waarde in het bereik C6: C12 komt overeen met de waarde in de cel G5, Usain Bolt. Zo ja, dan retourneert de functie het resultaat van "het bijbehorende rijnummer-6+1"; Geef anders een FALSE terug. Dus de resultaten zouden in een array als deze zijn: {ONWAAR;2;3;4;ONWAAR;ONWAAR;ONWAAR}.
• KLEIN(ALS(C6:C12=G5,RIJ (C6:C12)-RIJ(INDEX(C6:C12,1,1))+ 1),G6) = KLEIN({ONWAAR;2;3;4;ONWAAR;ONWAAR;ONWAAR},G6): Het fragment retourneert de 3rd (waarde in cel G6) kleinste waarde uit de array, dat is 4.
• INDEX(B6:B12,KLEIN(ALS(C6:C12=G5,RIJ (C6:C12)-RIJ(INDEX(C6:C12,1,1))+ 1),G6)) = INDEX(B6:B12,4) De INDEX-functie retourneert de 4e waarde in het jaarbereik B6: B12Dit is Beijing 2008.

Gerelateerde functies

Verwante formules