n-de overeenkomst ophalen met INDEX

Om de n-de overeenkomst van een waarde in een bereik te vinden en de bijbehorende gegevens op te halen, kunt u een formule gebruiken die is gebaseerd op de INDEX, RIJ, KLEINSTE en ALS functies.

Hoe haalt u met INDEX de informatie op die hoort bij de n-de overeenkomst van een waarde?

Om de informatie over de 3e overeenkomst van Usain Bolt in de hierboven getoonde tabel op te halen, combineert u de functies RIJ en ALS om een matrix te krijgen met de rijnummers van alle waarden voor Usain Bolt. Gebruik vervolgens de functie KLEINSTE om het rijnummer van de n-de overeenkomende waarde (zoals door u opgegeven) te bepalen. Voer dit resultaat ten slotte in de INDEX-functie in om de bijbehorende informatie uit dezelfde rij op te halen.

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 die u moet invoeren met Ctrl+Shift+Enter.

Om de informatie over de 3e overeenkomst van Usain Boltop te halen, kopieert of voert u de onderstaande formules in cellen G8 en G9 in en drukt u op Ctrl+Shift+Enterom de resultaten te verkrijgen:

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

Uitleg 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 functie RIJ retourneert het rijnummer van elke cel in het bereik C6:C12 als een matrix zoals deze: {6;7;8;9;10;11;12}.
• RIJ(INDEX(C6:C12,1,1)):De functies INDEX en RIJ geven het rijnummer van de eerste rij in het opzoekbereik C6:C12op dit Excel-werkblad, namelijk 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 overeenkomt met de waarde in cel G5, Usain Bolt. Indien ja, retourneert de functie het resultaat „bijbehorend rijnummer – 6 + 1”; anders retourneert deze ONWAAR. De resultaten vormen dus een matrix zoals deze: {ONWAAR;2;3;4;ONWAAR;ONWAAR;ONWAAR}.
• KLEINSTE()ALS(C6:C12=G5,)RIJ(C6:C12)-RIJ(INDEX(C6:C12,1,1))+1),G6)=KLEINSTE(){ONWAAR;2;3;4;ONWAAR;ONWAAR;ONWAAR},G6): Dit fragment retourneert de 3de (waarde in cel)G6) kleinste waarde uit de matrix, namelijk 4.
• INDEX(B6:B12,)KLEINSTE()ALS(C6:C12=G5,)RIJ(C6:C12)-RIJ(INDEX(C6:C12,1,1))+1),G6)) = INDEX(B6:B12,4)De INDEX-functie retourneert de 4de waarde in het bereik B6:B12, namelijk Beijing 2008.

Gerelateerde functies

Gerelateerde formules