Генеричка формула
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Резиме
Да бисте претраживали вредности помоћу ИНДЕКС и МАТЦХ, користећи више критеријума, можете користити формулу низа. У приказаном примеру, формула у Х8 је:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Објашњење
Ово је напреднија формула. За основе погледајте Како користити ИНДЕКС и МАТЦХ.
Обично је формула ИНДЕКС МАТЦХ конфигурисана са МАТЦХ подешеном да гледа кроз опсег од једне колоне и пружа подударање на основу задатих критеријума. Без спајања вредности у помоћном ступцу или у самој формули, не постоји начин да се наведе више од једног критеријума.
Ова формула заобилази ово ограничење користећи логичку логику за стварање низа јединица и нула које представљају редове који одговарају сва 3 критеријума, а затим помоћу МАТЦХ-а одговара првом пронађеном 1. Привремени низ јединица и нула генерише се помоћу овог исечка:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Овде упоређујемо ставке у Х5 са свим ставкама, величину у Х6 са свим величинама и боју у Х7 са свим бојама. Почетни резултат су три низа ТРУЕ / ФАЛСЕ резултата попут овог:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Савет: користите Ф9 да бисте видели ове резултате. Само одаберите израз у траци са формулама и притисните Ф9.
Математичка операција (множење) трансформише ТРУЕ ФАЛСЕ вредности у 1с и 0с:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Након множења имамо један овакав низ:
(0;0;1;0;0;0;0)
који се у функцију МАТЦХ уноси као низ претраживања, са вредношћу претраживања 1:
MATCH(1,(0;0;1;0;0;0;0))
У овом тренутку, формула је стандардна формула ИНДЕКС МАТЦХ. Функција МАТЦХ враћа 3 у ИНДЕКС:
=INDEX(E5:E11,3)
а ИНДЕКС враћа коначни резултат од 17,00 УСД.
Визуализација низа
Горе објашњени низови могу бити тешки за визуализацију. Слика испод приказује основну идеју. Колоне Б, Ц и Д одговарају подацима у примеру. Колона Ф настаје множењем три колоне заједно. То је низ који се предаје МАТЦХ.
Верзија без низа
Овој формули је могуће додати још ИНДЕКС, избегавајући потребу за уносом као формулу низа са цонтрол + схифт + ентер:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Функција ИНДЕКС може изворно обрађивати низове, па се други ИНДЕКС додаје само да би „ухватио“ низ створен логичком операцијом и вратио исти низ поново у МАТЦХ. Да би то урадио, ИНДЕКС је конфигурисан са нула редова и једном колоном. Трик нултих редова доводи до тога да ИНДЕКС врати колону 1 из низа (што је ионако већ једна колона).
Зашто бисте желели верзију која није арраи? Понекад људи забораве да унесу формулу низа са цонтрол + схифт + ентер, а формула даје нетачан резултат. Дакле, формула без низа је више „непробојна“. Међутим, компромис је сложенија формула.
Напомена: У програму Екцел 365 није потребно на посебан начин уносити формуле низова.
