17. peatükk · 17.4
Otsimine kahe tingimuse järgi korraga
VLOOKUP ja XLOOKUP otsivad ühe väärtuse järgi (vt VLOOKUP annab veateate #N/A: nii lahendad selle ja XLOOKUP: otsimine vasakule ja oma teatega). Praktikas otsid sageli kahe asja järgi korraga: L-suuruses kimbu hind, kliendi tariif kindlal aastal, kindlat värvi toote laoseis. Selleks on kolm head viisi ja kõik töötavad, kui vajutad lihtsalt Enter. Neljas viis, mida internetis sageli näeb, ei tööta Euro-Office'is niisama.
Näitena võtame Bloemisterij Het Zonnetje hinnakirja. Sama toode on seal mitu korda, eri suurustes:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Suurus | Hind |
| 2 | Kimp | S | 17,50 |
| 3 | Kimp | M | 24,50 |
| 4 | Kimp | L | 34,50 |
| 5 | Leinapärg | M | 65,00 |
| 6 | Leinapärg | L | 89,00 |
| 7 | Pruudikimp | M | 79,50 |
Otsitav toode on lahtris F2 (Kimp), suurus lahtris G2 (L). Otsime väärtust 34,50.
1. viis: XLOOKUP ühendatud väärtustega
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"ei leitud")
Märk & liidab teksti kokku. F2&"|"&G2 annab tulemuseks "Kimp|L". A2:A7&"|"&B2:B7 teeb loendi igast reast samasuguse kombinatsiooni ("Kimp|S", "Kimp|M" jne) ja XLOOKUP otsib nende hulgast. Vahele pandud püstkriips | hoiab ära, et kaks eri kombinatsiooni annaksid juhuslikult sama teksti. Kui kombinatsiooni pole, näiteks S-suuruses leinapärga, saad tulemuseks "ei leitud".

Kui vaatad pärast sisestamist valemiriba, on A2:A7 ja B2:B7 ees märk @. Selle lisab Euro-Office ise, tulemus on ikkagi õige.
2. viis: FILTER kõigi vastete jaoks
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"pole")
FILTER ei anna ainult esimest, vaid kõik read, mis vastavad mõlemale tingimusele. Kui kombinatsioon esineb kaks korda, näed mõlemat hinda üksteise all. Jäta seega valemi alla ruumi (vt Iseennast uuendavad loendid funktsioonidega FILTER, UNIQUE ja SORT). Kui soovid "ja" asemel "või", kasuta plussmärki: (A2:A7="Kimp")+(A2:A7="Leinapärg").
3. viis: SUMIFS, kui kombinatsioon on unikaalne
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Kõlab veidralt, sest sa ei taha midagi kokku liita. Aga kui iga kombinatsioon esineb vaid korra, on summa lihtsalt see üks hind. Eelis: SUMIFS on lihtne, kiire ja tuttav igale Exceli versioonile. Puudus: kui kombinatsiooni pole, saad teate asemel 0 (0,00 €). Ja kui kombinatsioon on kogemata kaks korda kirjas, saad mõlema hinna summa.
Mida leiad internetist: INDEX ja MATCH korrutamisega
Paljud Exceli veebilehed pakuvad valemit =INDEX(C2:C7;MATCH(1;(A2:A7=F2)*(B2:B7=G2);0)). Euro-Office'is annab see valem tulemuseks #N/A, kui vajutad lihtsalt Enter, isegi kui kombinatsioon on olemas. Kui lõpetad selle klahvidega Ctrl+Shift+Enter, saab valem looksulud {…} ja töötab. Seda on lihtne unustada, seega kasuta pigem ülal kirjeldatud XLOOKUP-i või FILTER-it.
Otsimine rea ja veeru järgi
Kui su hinnakiri on tabel, kus tooted on üksteise all ja suurused kõrvuti (maatriks), kasuta INDEX-it koos kahe MATCH-iga. Kui tooted on vahemikus A11:A13, suurused S, M ja L vahemikus B10:D10 ja hinnad vahemikus B11:D13:
=INDEX(B11:D13;MATCH(F2;A11:A13;0);MATCH(G2;B10:D10;0))
Esimene MATCH otsib toote rea, teine suuruse veeru. INDEX võtab lahtri nende ristumiskohast.
Millise valida?
| Olukord | Parim valik |
|---|---|
| Üks vastus koos selge teatega, kui seda pole | XLOOKUP ühendatud väärtustega |
| Fail peab töötama ka vanas Excelis | SUMIFS |
| Vasteid võib olla mitu | FILTER |
| Hinnatabel, kus suurused on veerupäistes | INDEX koos kahe MATCH-iga |
Lõksud
- #N/A, kuigi kombinatsioon on tõesti olemas: sageli on väärtuse järel tühik ("Kimp ") või on arv salvestatud tekstina. Vt VLOOKUP annab veateate #N/A: nii lahendad selle.
- Eri pikkusega vahemikud: =XLOOKUP(F2;A2:A7;C2:C50) annab tulemuseks #VALUE!. Lase kõigil vahemikel algada ja lõppeda samal real.
- Terved veerud (A:A) teevad nendes valemites suure faili aeglaseks, sest Euro-Office ühendab siis miljon rida. Kasuta kindlat vahemikku, näiteks A2:A500. Vt Aeglase arvutustabeli kiiremaks muutmine.
- Kolleeg, kellel on Excel 2019 või vanem, näeb XLOOKUP-i ja FILTER-i puhul viga #NAME?.
