Generička formula
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Sažetak
Za traženje vrijednosti s INDEX i MATCH, koristeći više kriterija, možete koristiti formulu niza. U prikazanom primjeru formula u H8 je:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter, osim u programu Excel 365.
Obrazloženje
Ovo je naprednija formula. Za osnove pogledajte Kako koristiti INDEX i MATCH.
Obično je formula INDEX MATCH konfigurirana s MATCH set postavljenim da gleda kroz raspon od jednog stupca i pruža podudaranje na temelju zadanih kriterija. Bez spajanja vrijednosti u pomoćnom stupcu ili u samoj formuli, ne postoji način da se navede više od jednog kriterija.
Ova formula zaobilazi ovo ograničenje upotrebom logičke logike za stvaranje niza jedinica i nula za predstavljanje redaka koji odgovaraju sva 3 kriterija, a zatim pomoću MATCH-a za podudaranje s prvim 1 pronađenim. Privremeni niz jedinica i nula generira se pomoću ovog isječka:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Ovdje uspoređujemo stavku u H5 sa svim stavkama, veličinu u H6 sa svim veličinama i boju u H7 sa svim bojama. Početni rezultat su tri niza TRUE / FALSE rezultata poput ovog:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Savjet: upotrijebite F9 da biste vidjeli ove rezultate. Samo odaberite izraz na traci s formulama i pritisnite F9.
Matematička operacija (množenje) pretvara TRUE FALSE vrijednosti u 1s i 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Nakon množenja imamo jedan takav niz:
(0;0;1;0;0;0;0)
koji se dovodi u funkciju MATCH kao niz pretraživanja, s vrijednošću pretraživanja 1:
MATCH(1,(0;0;1;0;0;0;0))
U ovom trenutku formula je standardna formula INDEX MATCH. Funkcija MATCH vraća 3 u INDEX:
=INDEX(E5:E11,3)
a INDEX vraća konačni rezultat od 17,00 USD.
Vizualizacija niza
Gore objašnjene nizove može biti teško vizualizirati. Slika ispod prikazuje osnovnu ideju. Stupci B, C i D odgovaraju podacima u primjeru. Stupac F nastaje množenjem tri stupca zajedno. To je niz koji se predaje MATCH-u.
Verzija bez niza
Ovoj je formuli moguće dodati još INDEX, izbjegavajući potrebu za unosom kao matrične formule s control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Funkcija INDEX može izvorno obrađivati nizove, pa se drugi INDEX dodaje samo za "hvatanje" niza stvorenog logičkom logičkom operacijom i vraćanje istog polja ponovno u MATCH. Da biste to učinili, INDEX je konfiguriran s nula redaka i jednim stupcem. Trik nultih redova uzrokuje da INDEX vrati stupac 1 iz niza (koji je ionako već jedan stupac).
Zašto biste željeli verziju koja nije array? Ponekad ljudi zaborave unijeti formulu niza s control + shift + enter, a formula daje netočan rezultat. Dakle, formula bez polja je više "neprobojna". Međutim, kompromis je složenija formula.
Napomena: U programu Excel 365 nije potrebno na poseban način unositi formule polja.
