Excel-formel: Få n: a matchning med INDEX / MATCH -

Generisk formel

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

Sammanfattning

För att hämta flera matchande värden från en uppsättning data med en formel kan du använda funktionerna IF och SMALL för att räkna ut radnumret för varje matchning och mata tillbaka det värdet till INDEX. I exemplet som visas är formeln i I7:

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

Där namngivna intervall är amts (D4: D11), id (I3) och ids (C4: C11).

Observera att detta är en matrisformel och måste anges med Control + Shift + Enter.

Förklaring

I grunden är denna formel helt enkelt en INDEX-formel som hämtar värdet i en matris vid en given position. Värdet för n tillhandahålls i kolumn H och allt "tunga" arbete som formeln gör är att räkna ut raden från vilken ett värde ska hämtas, där raden motsvarar "nth" matchning.

IF-funktionen gör jobbet med att räkna ut vilka rader som innehåller en matchning och funktionen SMALL returnerar det n: te värdet från listan. Inuti IF är det logiska testet:

ids=id

som ger denna array:

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

Observera att kund-id-matchningarna på 1: a och 4: e positionen visas som SANT. Argumentet "value if true" i IF genererar en lista med relativa radnummer med detta uttryck:

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

som producerar denna array:

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

Denna matris filtreras sedan av de logiska testresultaten och IF-funktionen returnerar följande matrisresultat:

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

Observera att vi har giltiga radnummer för rad 1 och rad 2.

Denna matris bearbetas sedan av SMALL, som är konfigurerad för att använda värden i kolumn H för att returnera "nth" -värden. SMALL-funktionen ignorerar automatiskt de logiska värdena SANT och FALSK i matrisen. I slutändan minskar formlerna till:

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

Hanteringsfel

När det inte finns fler matchningar för ett visst id kommer funktionen SMALL att returnera ett #NUM-fel. Du kan hantera detta fel med IFERROR-funktionen eller genom att lägga till logik för att räkna matchningar och avbryta bearbetning när siffran i kolumn H är större än matchningsantalet. Exemplet här visar ett tillvägagångssätt.