Excel formula: Pridobite n-to ujemanje z INDEX / MATCH -

Splošna formula

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

Povzetek

Če želite pridobiti več ujemajočih se vrednosti iz nabora podatkov s formulo, lahko s funkcijama IF in SMALL ugotovite številko vrstice vsakega ujemanja in to vrednost vrnete v INDEX. V prikazanem primeru je formula v I7:

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

Kjer so imenovani obsegi amt (D4: D11), id (I3) in id (C4: C11).

Upoštevajte, da je to formula matrike in jo je treba vnesti s tipkama Control + Shift + Enter.

Pojasnilo

V bistvu je ta formula preprosto INDEX formula, ki pridobi vrednost v matriki na določenem položaju. Vrednost za n je podana v stolpcu H in vse "težko" delo, ki ga opravi formula, je ugotoviti vrstico, iz katere je treba pridobiti vrednost, kjer vrstica ustreza "n-ti" ujemanju.

Funkcija IF ugotovi, katere vrstice vsebujejo ujemanje, funkcija SMALL pa vrne n-to vrednost s tega seznama. Znotraj IF je logični test:

ids=id

ki daje to matriko:

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

Upoštevajte ujemanja id stranke na 1. in 4. mestu, ki sta prikazani kot TRUE. Argument "value if true" v IF ustvari seznam relativnih številk vrstic s tem izrazom:

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

ki ustvari to matriko:

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

To matriko nato "filtrirajo" rezultati logičnega preizkusa, funkcija IF pa vrne naslednji rezultat matrike:

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

Upoštevajte, da imamo veljavne številke vrstic za vrstico 1 in vrstico 2.

Nato to matriko obdela SMALL, ki je konfigurirana za uporabo vrednosti v stolpcu H za vrnitev "n-ih" vrednosti. Funkcija SMALL samodejno prezre logični vrednosti TRUE in FALSE v matriki. Na koncu se formule zmanjšajo na:

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

Napake pri ravnanju

Ko za dani ID ni več zadetkov, bo funkcija SMALL vrnila napako #NUM. To napako lahko odpravite s funkcijo IFERROR ali z dodajanjem logike za štetje ujemanj in prekinitev obdelave, ko je število v stolpcu H večje od števila ujemanj. Primer tukaj kaže en pristop.