Poglavlje 17 · 17.4
Pretraživanje prema dva uvjeta istodobno
VLOOKUP i XLOOKUP pretražuju prema jednoj vrijednosti (pogledajte VLOOKUP vraća #N/A: evo kako to riješiti i XLOOKUP: pretraživanje ulijevo i s vlastitom porukom). U praksi često tražite prema dvjema stvarima istodobno: cijenu buketa u veličini L, tarifu klijenta u određenoj godini, zalihu proizvoda u određenoj boji. Za to postoje tri dobra načina, a svi rade s običnim Enter. Četvrti način, koji ćete često vidjeti na internetu, u Euro-Officeu ne radi bez dodatnog koraka.
Kao primjer uzimamo cjenik cvjećarnice Bloemisterij Het Zonnetje. Isti proizvod u njemu se pojavljuje više puta, u različitim veličinama:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Veličina | Cijena |
| 2 | Buket | S | 17,50 |
| 3 | Buket | M | 24,50 |
| 4 | Buket | L | 34,50 |
| 5 | Žalobni aranžman | M | 65,00 |
| 6 | Žalobni aranžman | L | 89,00 |
| 7 | Vjenčani buket | M | 79,50 |
Traženi proizvod nalazi se u ćeliji F2 (Buket), a veličina u G2 (L). Tražimo 34,50.
Način 1: XLOOKUP sa spojenim vrijednostima
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"nije pronađeno")
Znak & spaja tekst. F2&"|"&G2 postaje "Buket|L". A2:A7&"|"&B2:B7 od svakog retka na popisu također stvara takvu kombinaciju ("Buket|S", "Buket|M" i tako dalje), a XLOOKUP pretražuje među njima. Okomita crta | između vrijednosti sprječava da dvije različite kombinacije slučajno daju isti tekst. Ako kombinacija ne postoji, na primjer žalobni aranžman u veličini S, dobit ćete "nije pronađeno".

Ako nakon upisivanja pogledate traku formule, ispred A2:A7 i B2:B7 stoji znak @. Njega Euro-Office dodaje sam; rezultat je ipak točan.
Način 2: FILTER za sve pogotke
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"nema")
FILTER ne vraća samo prvi, nego sve retke koji zadovoljavaju oba uvjeta. Ako se kombinacija pojavljuje dvaput, obje cijene vidjet ćete jednu ispod druge. Stoga se pobrinite da ispod formule ima mjesta (pogledajte Popisi koji se sami ažuriraju uz FILTER, UNIQUE i SORT). Za "ili" umjesto "i" koristite znak plus: (A2:A7="Buket")+(A2:A7="Žalobni aranžman").
Način 3: SUMIFS ako je kombinacija jedinstvena
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Zvuči neobično, jer ništa ne želite zbrajati. No ako se svaka kombinacija pojavljuje samo jednom, zbroj je upravo ta jedna cijena. Prednost: SUMIFS je jednostavan, brz i poznaje ga svaka verzija Excela. Nedostatak: ako kombinacija ne postoji, dobit ćete 0 (0,00 €) umjesto poruke. A ako se kombinacija greškom pojavljuje dvaput, dobit ćete zbroj obiju cijena.
Što ćete pronaći na internetu: INDEX i MATCH s množenjem
Mnoge stranice o Excelu navode =INDEX(C2:C7;MATCH(1;(A2:A7=F2)*(B2:B7=G2);0)). U Euro-Officeu ta formula s običnim Enter daje #N/A, čak i kada kombinacija postoji. Ako je potvrdite s Ctrl+Shift+Enter, formula dobiva vitičaste zagrade {…} i tada radi. To se lako zaboravi, stoga radije koristite XLOOKUP ili FILTER opisane gore.
Pretraživanje po retku i stupcu
Ako je Vaš cjenik tablica s proizvodima jedan ispod drugoga i veličinama jedna pored druge (matrica), koristite INDEX s dvije funkcije MATCH. S proizvodima u A11:A13, veličinama S, M i L u B10:D10 te cijenama u B11:D13:
=INDEX(B11:D13;MATCH(F2;A11:A13;0);MATCH(G2;B10:D10;0))
Prva funkcija MATCH traži redak proizvoda, a druga stupac veličine. INDEX uzima ćeliju na njihovu sjecištu.
Koji način odabrati?
| Situacija | Najbolji izbor |
|---|---|
| Jedan odgovor, s urednom porukom ako ne postoji | XLOOKUP sa spojenim vrijednostima |
| Datoteka mora raditi i u starijem Excelu | SUMIFS |
| Može biti više pogodaka | FILTER |
| Tablica cijena s veličinama kao zaglavljima stupaca | INDEX s dvije funkcije MATCH |
Zamke
- #N/A iako kombinacija stvarno postoji: često je riječ o razmaku iza vrijednosti ("Buket ") ili o broju koji je spremljen kao tekst. Pogledajte VLOOKUP vraća #N/A: evo kako to riješiti.
- Rasponi nejednake duljine: =XLOOKUP(F2;A2:A7;C2:C50) daje #VALUE!. Neka svi rasponi počinju i završavaju u istom retku.
- Cijeli stupci (A:A) u ovim formulama usporavaju veliku datoteku jer Euro-Office tada spaja milijun redaka. Koristite fiksni raspon kao što je A2:A500. Pogledajte Kako ubrzati sporu proračunsku tablicu.
- Kolega s Excelom 2019 ili starijim kod funkcija XLOOKUP i FILTER vidjet će #NAME?.
