Poglavje 17 · 17.4
Iskanje po dveh pogojih hkrati
VLOOKUP in XLOOKUP iščeta po eni vrednosti (glejte VLOOKUP vrne #N/A: kako to odpraviti in XLOOKUP: iskanje v levo in z lastnim sporočilom). V praksi pogosto iščete po dveh stvareh hkrati: ceno šopka velikosti L, tarifo stranke v določenem letu ali zalogo izdelka v določeni barvi. Za to obstajajo trije dobri načini, ki vsi delujejo z običajno tipko Enter. Četrti način, ki ga pogosto najdete na internetu, v Euro-Office ne deluje kar tako.
Kot primer vzemimo cenik cvetličarne Bloemisterij Het Zonnetje. Isti izdelek je v njem večkrat, v različnih velikostih:
| A | B | C | |
|---|---|---|---|
| 1 | Izdelek | Velikost | Cena |
| 2 | Šopek | S | 17,50 |
| 3 | Šopek | M | 24,50 |
| 4 | Šopek | L | 34,50 |
| 5 | Žalni aranžma | M | 65,00 |
| 6 | Žalni aranžma | L | 89,00 |
| 7 | Poročni šopek | M | 79,50 |
Iskani izdelek je v celici F2 (Šopek), velikost pa v G2 (L). Iščemo 34,50.
1. način: XLOOKUP z združenimi vrednostmi
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"ni najdeno")
Znak & združi besedilo. F2&"|"&G2 postane "Šopek|L". A2:A7&"|"&B2:B7 iz vsake vrstice seznama prav tako naredi takšno kombinacijo ("Šopek|S", "Šopek|M" in tako naprej), XLOOKUP pa išče med njimi. Navpična črta | vmes prepreči, da bi dve različni kombinaciji po naključju dali enako besedilo. Če kombinacija ne obstaja, na primer žalni aranžma velikosti S, dobite "ni najdeno".

Če po vnosu pogledate v vrstico za formule, je pred A2:A7 in B2:B7 znak @. Tega Euro-Office doda samodejno; rezultat je kljub temu pravilen.
2. način: FILTER za vse zadetke
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"ni")
FILTER ne vrne le prve, temveč vse vrstice, ki izpolnjujejo oba pogoja. Če se kombinacija pojavi dvakrat, vidite obe ceni drugo pod drugo. Zato poskrbite, da je pod formulo dovolj prostora (glejte Seznami, ki se sami posodabljajo, s funkcijami FILTER, UNIQUE in SORT). Za »ali« namesto »in« uporabite znak plus: (A2:A7="Šopek")+(A2:A7="Žalni aranžma").
3. način: SUMIFS, ko je kombinacija edinstvena
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Sliši se nenavadno, saj ničesar ne želite seštevati. A če se vsaka kombinacija pojavi samo enkrat, je vsota preprosto tista ena cena. Prednost: SUMIFS je preprost, hiter in ga pozna vsaka različica Excela. Slabost: če kombinacija ne obstaja, namesto obvestila dobite 0 (0,00 €). Če pa je kombinacija po pomoti vpisana dvakrat, dobite seštevek obeh cen.
Kar najdete na internetu: INDEX in MATCH z množenjem
Številne spletne strani o Excelu navajajo =INDEX(C2:C7;MATCH(1;(A2:A7=F2)*(B2:B7=G2);0)). V Euro-Office ta formula z običajno tipko Enter vrne #N/A, tudi če kombinacija obstaja. Če jo zaključite s Ctrl+Shift+Enter, formula dobi zavite oklepaje {…} in deluje. To pa hitro pozabite, zato raje uporabite XLOOKUP ali FILTER iz zgornjih načinov.
Iskanje po vrstici in stolpcu
Če je Vaš cenik tabela z izdelki drug pod drugim in velikostmi drugo ob drugi (matrika), uporabite INDEX z dvema funkcijama MATCH. Z izdelki v A11:A13, velikostmi S, M in L v B10:D10 ter cenami v B11:D13:
=INDEX(B11:D13;MATCH(F2;A11:A13;0);MATCH(G2;B10:D10;0))
Prva funkcija MATCH poišče vrstico izdelka, druga stolpec velikosti. INDEX vzame celico na njunem presečišču.
Kateri način izbrati?
| Situacija | Najboljša izbira |
|---|---|
| En odgovor, z jasnim obvestilom, če ne obstaja | XLOOKUP z združenimi vrednostmi |
| Datoteka mora delovati tudi v starejšem Excelu | SUMIFS |
| Zadetkov je lahko več | FILTER |
| Cenik z velikostmi kot naslovi stolpcev | INDEX z dvema funkcijama MATCH |
Pasti
- #N/A, čeprav kombinacija res obstaja: pogosto gre za presledek za vrednostjo ("Šopek ") ali za število, shranjeno kot besedilo. Glejte VLOOKUP vrne #N/A: kako to odpraviti.
- Obsegi neenake dolžine: =XLOOKUP(F2;A2:A7;C2:C50) vrne #VALUE!. Poskrbite, da se vsi obsegi začnejo in končajo v isti vrstici.
- Celotni stolpci (A:A) v teh formulah upočasnijo veliko datoteko, saj Euro-Office takrat združuje milijon vrstic. Uporabite fiksen obseg, na primer A2:A500. Glejte Pospešitev počasne preglednice.
- Sodelavec z Excelom 2019 ali starejšim pri XLOOKUP in FILTER vidi #NAME?.
