Excel vzorec: Získejte n-tou shodu s INDEX / MATCH -

Obecný vzorec

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

souhrn

Chcete-li načíst více shodných hodnot ze sady dat pomocí vzorce, můžete pomocí funkcí IF a SMALL zjistit počet řádků každé shody a poslat tuto hodnotu zpět na INDEX. V zobrazeném příkladu je vzorec v I7:

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

Kde pojmenované rozsahy jsou amts (D4: D11), id (I3) a ids (C4: C11).

Všimněte si, že se jedná o maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.

Vysvětlení

V jádru je tento vzorec jednoduše vzorcem INDEX, který načte hodnotu v poli na dané pozici. Hodnota n je uvedena ve sloupci H a veškerá „těžká“ práce, kterou vzorec dělá, je zjistit řádek, ze kterého se má načíst hodnota, kde řádek odpovídá „n-té“ shodě.

Funkce IF provádí zjišťování, které řádky obsahují shodu, a funkce SMALL vrací n-tou hodnotu z tohoto seznamu. Uvnitř IF je logický test:

ids=id

což dává toto pole:

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

Všimněte si, že ID zákazníka se shoduje na 1. a 4. pozici, které se zobrazují jako PRAVDA. Argument "value if true" v IF generuje seznam relativních čísel řádků s tímto výrazem:

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

který vytváří toto pole:

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

Toto pole je poté "filtrováno" podle výsledků logického testu a funkce IF vrátí následující výsledek pole:

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

Všimněte si, že máme platná čísla řádků pro řádek 1 a řádek 2.

Toto pole je poté zpracováno SMALL, který je nakonfigurován pro použití hodnot ve sloupci H k vrácení „n-tých“ hodnot. Funkce SMALL automaticky ignoruje logické hodnoty TRUE a FALSE v poli. Nakonec se vzorce redukují na:

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

Zpracování chyb

Jakmile pro dané ID již neexistují žádné další shody, funkce SMALL vrátí chybu # ČÍSLO. Tuto chybu můžete zpracovat pomocí funkce IFERROR nebo přidáním logiky k počítání shod a přerušení zpracování, jakmile je číslo ve sloupci H větší než počet shody. Příklad zde ukazuje jeden přístup.