Excel-formel: Få nte kamp med INDEX / MATCH -

Generisk formel

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

Sammendrag

For å hente flere samsvarende verdier fra et datasett med en formel, kan du bruke IF- og SMALL-funksjonene til å finne ut radnummeret for hvert treff og mate den verdien tilbake til INDEKS. I eksemplet vist er formelen i I7:

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

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

Merk at dette er en matriseformel og må angis med Control + Shift + Enter.

Forklaring

I kjernen er denne formelen ganske enkelt en INDEX-formel som henter verdien i en matrise på en gitt posisjon. Verdien for n er gitt i kolonne H, og alt det "tunge" arbeidet som formelen gjør, er å finne ut raden du skal hente en verdi fra, der raden tilsvarer "nth" -match.

IF-funksjonen gjør arbeidet med å finne ut hvilke rader som inneholder et treff, og SMALL-funksjonen returnerer den nte verdien fra denne listen. Inne i IF er den logiske testen:

ids=id

som gir denne matrisen:

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

Legg merke til at kunde-ID-kampene på 1. og 4. plassering, som vises som SANT. Argumentet "verdi hvis sant" i IF genererer en liste over relative radnumre med dette uttrykket:

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

som produserer denne matrisen:

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

Denne matrisen blir deretter "filtrert" av de logiske testresultatene, og IF-funksjonen returnerer følgende matriseresultat:

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

Merk at vi har gyldige radnumre for rad 1 og rad 2.

Denne matrisen behandles deretter av SMALL, som er konfigurert til å bruke verdier i kolonne H for å returnere "nth" -verdier. SMALL-funksjonen ignorerer automatisk de logiske verdiene SANN og FALSK i matrisen. Til slutt reduserer formlene til:

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

Håndteringsfeil

Når det ikke er flere treff for en gitt id, vil SMALL-funksjonen returnere en #NUM-feil. Du kan håndtere denne feilen med IFERROR-funksjonen, eller ved å legge til logikk for å telle treff og avbryte behandlingen når tallet i kolonne H er større enn antall teller. Eksemplet her viser en tilnærming.