Excel-formel: Få det nte match med INDEX / MATCH -

Generisk formel

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Resumé

For at hente flere matchende værdier fra et datasæt med en formel kan du bruge IF og SMALL-funktionerne til at finde ud af række nummer for hvert match og føre denne værdi tilbage til INDEX. I det viste eksempel er formlen i I7:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Hvor navngivne områder er amts (D4: D11), id (I3) og ids (C4: C11).

Bemærk, at dette er en matrixformel og skal indtastes med Control + Shift + Enter.

Forklaring

Kernen er denne formel simpelthen en INDEX-formel, der henter værdien i en matrix på en given position. Værdien for n leveres i kolonne H, og alt det "tunge" arbejde, som formlen udfører, er at finde ud af rækken, hvorfra en værdi skal hentes, hvor rækken svarer til "nth" -match.

IF-funktionen udfører arbejdet med at finde ud af, hvilke rækker der indeholder et match, og funktionen SMALL returnerer den n-værdi fra denne liste. Inde i IF er den logiske test:

ids=id

som giver denne matrix:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Bemærk, at kunde-id-matches på 1. og 4. position, der vises som SAND. Argumentet "værdi hvis det er sandt" i IF genererer en liste med relative række numre med dette udtryk:

ROW(ids)-ROW(INDEX(ids,1,1))+1

der producerer denne matrix:

(1;2;3;4;5;6;7)

Denne matrix "filtreres" derefter af de logiske testresultater, og IF-funktionen returnerer følgende matrixresultat:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Bemærk, at vi har gyldige rækkenumre til række 1 og række 2.

Denne matrix behandles derefter af SMALL, som er konfigureret til at bruge værdier i kolonne H til at returnere "nth" -værdier. SMALL-funktionen ignorerer automatisk de logiske værdier SAND og FALSK i arrayet. I sidste ende reduceres formlerne til:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Håndteringsfejl

Når der ikke er flere matches for et givet id, vil SMALL-funktionen returnere en #NUM-fejl. Du kan håndtere denne fejl med IFERROR-funktionen eller ved at tilføje logik til at tælle kampe og afbryde behandlingen, når tallet i kolonne H er større end matchantalet. Eksemplet her viser en tilgang.