Generička formula
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Sažetak
Pregled velikih i malih slova u Excelu
Prema zadanim postavkama, standardna pretraživanja u Excelu ne razlikuju velika i mala slova. I VLOOKUP i INDEX / MATCH jednostavno će vratiti prvo podudaranje, zanemarujući slučaj.
Izravni način zaobilaženja ovog ograničenja je uporaba formule niza temeljene na INDEX / MATCH s EXACT. Međutim, ako tražite samo numeričke vrijednosti, SUMPRODUCT + EXACT također nudi zanimljiv i fleksibilan način pretraživanja s obzirom na velika i mala slova.
U primjeru koristimo sljedeću formulu
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Iako je ova formula formula niza, ne treba je unositi Control + Shift + Enter, jer SUMPRODUCT obrađuje nizove nativno.
Obrazloženje
SUMPRODUCT je dizajniran za rad s nizovima koje množi, a zatim zbraja.
U ovom smo slučaju dva niza sa SUMPRODUCT: B3: B8 i C3: C8. Trik je u izvođenju testa na vrijednostima u stupcu B, a zatim pretvoriti dobivene vrijednosti TRUE / FALSE u 1 i 0. Test izvodimo s TOČNO tako:
EXACT(E3,B3:B8)
Koji stvara ovaj niz:
(NETOČNO; NETOČNO; ISTINITO; NETOČNO; NETOČNO; NETOČNO)
Imajte na umu da je prava vrijednost na poziciji 3 naše podudaranje. Zatim koristimo dvostruko negativno (tj. - što je tehnički "dvostruko unarno") da prisilimo ove vrijednosti TRUE / FALSE u 1 i 0. Rezultat je ovaj niz:
(0; 0; 1; 0; 0; 0)
U ovom trenutku izračuna, formula SUMPRODUCT izgleda ovako:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT tada jednostavno množi stavke u svakom nizu da bi se dobio konačni niz:
(0; 0; 775; 0; 0; 0)
Koji SUMPRODUCT tada zbroji i vrati 775.
Dakle, suština ove formule je da se vrijednosti FALSE koriste za poništavanje svih ostalih vrijednosti. Jedine vrijednosti koje su preživjele su one koje su bile ISTINA.
Imajte na umu da, jer koristimo SUMPRODUCT, ova formula dolazi s jedinstvenim preokretom: ako postoji više podudaranja, SUMPRODUCT će vratiti zbroj tih podudaranja. Ovo može i ne mora biti ono što želite, pa pripazite očekujete li više podudaranja!
Zapamtite, ova formula radi samo za numeričke vrijednosti, jer SUMPRODUCT ne obrađuje tekst. Ako želite dohvatiti tekst, upotrijebite INDEX / MATCH + EXACT.
