Excel formula: Nabavite n-to podudaranje pomoću INDEX / MATCH -

Generička formula

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

Sažetak

Da biste dohvatili više podudarnih vrijednosti iz skupa podataka pomoću formule, možete koristiti funkcije IF i SMALL za utvrđivanje broja retka svakog podudaranja i vraćanje te vrijednosti natrag u INDEX. U prikazanom primjeru formula u I7 je:

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

Gdje su imenovani rasponi amts (D4: D11), id (I3) i id (C4: C11).

Imajte na umu da je ovo formula niza i mora se unijeti s Control + Shift + Enter.

Obrazloženje

U osnovi, ova je formula jednostavno INDEX formula koja dohvaća vrijednost u nizu na danom položaju. Vrijednost za n navedena je u stupcu H, a sav "težak" posao koji formula radi je utvrditi redak iz kojeg će se dohvatiti vrijednost, gdje redak odgovara "n-tom" podudaranju.

IF funkcija utvrđuje koji redovi sadrže podudaranje, a SMALL funkcija vraća n-tu vrijednost s tog popisa. Unutar IF-a logični test je:

ids=id

što daje ovaj niz:

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

Imajte na umu da se identifikacije kupaca podudaraju na 1. i 4. poziciji, koje se prikazuju kao ISTINA. Argument "value if true" u IF generira popis relativnih brojeva redaka s ovim izrazom:

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

koji proizvodi ovaj niz:

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

Zatim se taj niz "filtrira" prema rezultatima logičkog ispitivanja, a funkcija IF vraća sljedeći rezultat niza:

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

Imajte na umu da imamo važeće brojeve redaka za redak 1 i red 2.

Taj niz zatim obrađuje SMALL, koji je konfiguriran da koristi vrijednosti u stupcu H za vraćanje "n-ih" vrijednosti. Funkcija SMALL automatski zanemaruje logičke vrijednosti TRUE i FALSE u polju. Na kraju se formule smanjuju na:

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

Pogreške u rukovanju

Nakon što više nema podudaranja za zadani ID, funkcija SMALL vratit će pogrešku #NUM. Ovu pogrešku možete riješiti pomoću funkcije IFERROR ili dodavanjem logike za brojanje podudaranja i prekid obrade kada je broj u stupcu H veći od broja podudaranja. Primjer ovdje pokazuje jedan pristup.