Екцел формула: Набавите н-то подударање помоћу ИНДЕКС / МАТЦХ -

Генеричка формула

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

Резиме

Да бисте преузели више подударних вредности из скупа података помоћу формуле, можете да користите функције ИФ и СМАЛЛ да бисте утврдили број реда сваког подударања и вратили ту вредност у ИНДЕКС. У приказаном примеру, формула у И7 је:

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

Тамо где су именовани опсези амт (Д4: Д11), ид (И3) и ид (Ц4: Ц11).

Имајте на уму да је ово формула низа и мора се унети са Цонтрол + Схифт + Ентер.

Објашњење

У основи, ова формула је једноставно ИНДЕКС формула која преузима вредност у низу на датој позицији. Вредност за н је наведена у колони Х, а сав „тежак“ посао који формула ради је да открије ред из ког ће се добити вредност, где ред одговара „н-том“ подударању.

ИФ функција утврђује који редови садрже подударање, а СМАЛЛ функција враћа н-ту вредност са те листе. Унутар ИФ логички тест је:

ids=id

што даје овај низ:

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

Имајте на уму да се идентификација купаца подудара на 1. и 4. позицији, која се приказују као ИСТИНА. Аргумент "валуе иф труе" у ИФ генерише листу релативних бројева редова са овим изразом:

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

који производи овај низ:

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

Овај низ се онда „филтрира“ од резултата логичког теста, а функција ИФ враћа следећи резултат низа:

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

Имајте на уму да имамо важеће бројеве редова за ред 1 и ред 2.

Овај низ затим обрађује СМАЛЛ, који је конфигурисан да користи вредности у колони Х за враћање „нтх“ вредности. Функција СМАЛЛ аутоматски занемарује логичке вредности ТРУЕ и ФАЛСЕ у пољу. На крају, формуле се смањују на:

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

Руковање грешкама

Једном када више нема подударања за дати ИД, функција СМАЛЛ ће вратити грешку #НУМ. Ову грешку можете решити помоћу функције ИФЕРРОР или додавањем логике за бројање подударања и прекид обраде када је број у колони Х већи од броја подударања. Пример овде показује један приступ.