Kapitola 17 · 17.4
Vyhľadávanie podľa dvoch podmienok naraz
VLOOKUP a XLOOKUP vyhľadávajú podľa jednej hodnoty (pozrite VLOOKUP vracia #N/A: ako to vyriešiť a XLOOKUP: vyhľadávanie doľava a s vlastným hlásením). V praxi však často hľadáte podľa dvoch vecí naraz: cenu kytice vo veľkosti L, tarifu zákazníka v určitom roku alebo zásobu produktu v určitej farbe. Na to existujú tri dobré spôsoby, ktoré všetky fungujú s obyčajným Enter. Štvrtý spôsob, ktorý často nájdete na internete, v Euro-Office nefunguje bez ďalšieho kroku.
Ako príklad použijeme cenník kvetinárstva Bloemisterij Het Zonnetje. Ten istý produkt sa v ňom nachádza viackrát, v rôznych veľkostiach:
| A | B | C | |
|---|---|---|---|
| 1 | Produkt | Veľkosť | Cena |
| 2 | Boeket | S | 17,50 |
| 3 | Boeket | M | 24,50 |
| 4 | Boeket | L | 34,50 |
| 5 | Rouwstuk | M | 65,00 |
| 6 | Rouwstuk | L | 89,00 |
| 7 | Bruidsboeket | M | 79,50 |
Hľadaný produkt je v bunke F2 (Boeket), veľkosť v bunke G2 (L). Hľadáme hodnotu 34,50.
Spôsob 1: XLOOKUP so spojenými hodnotami
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"nenájdené")
Znak & spája text dohromady. Z F2&"|"&G2 vznikne "Boeket|L". A2:A7&"|"&B2:B7 vytvorí takúto kombináciu aj z každého riadka zoznamu ("Boeket|S", "Boeket|M" atď.) a XLOOKUP v nich vyhľadáva. Zvislá čiara | medzi hodnotami zabráni tomu, aby dve rôzne kombinácie náhodou vytvorili rovnaký text. Ak kombinácia neexistuje, napríklad Rouwstuk vo veľkosti S, dostanete "nenájdené".

Ak sa po napísaní pozriete do riadka vzorcov, uvidíte pred A2:A7 a B2:B7 znak @. Ten tam doplní Euro-Office sám; výsledok je napriek tomu správny.
Spôsob 2: FILTER pre všetky zhody
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"žiadne")
FILTER nevráti len prvý, ale všetky riadky, ktoré spĺňajú obe podmienky. Ak sa kombinácia vyskytuje dvakrát, uvidíte obe ceny pod sebou. Preto zabezpečte, aby bolo pod vzorcom voľné miesto (pozrite Zoznamy, ktoré sa samy aktualizujú, pomocou FILTER, UNIQUE a SORT). Pre „alebo“ namiesto „a“ použite znamienko plus: (A2:A7="Boeket")+(A2:A7="Rouwstuk").
Spôsob 3: SUMIFS, ak je kombinácia jedinečná
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Znie to zvláštne, pretože nechcete nič sčítavať. Ak sa však každá kombinácia vyskytuje iba raz, súčet je jednoducho tá jedna cena. Výhoda: SUMIFS je jednoduchá, rýchla a pozná ju každá verzia Excelu. Nevýhoda: ak kombinácia neexistuje, dostanete 0 (€ 0,00) namiesto hlásenia. A ak je kombinácia omylom v zozname dvakrát, dostanete súčet oboch cien.
Čo nájdete na internete: INDEX a MATCH s násobením
Mnohé stránky o Exceli uvádzajú =INDEX(C2:C7;MATCH(1;(A2:A7=F2)*(B2:B7=G2);0)). V Euro-Office vráti tento vzorec s obyčajným Enter chybu #N/A, aj keď kombinácia existuje. Ak ho potvrdíte klávesmi Ctrl+Shift+Enter, vzorec dostane zložené zátvorky {…} a funguje. Na to sa však ľahko zabudne, preto radšej použite XLOOKUP alebo FILTER uvedené vyššie.
Vyhľadávanie podľa riadka aj stĺpca
Ak máte cenník ako tabuľku s produktmi pod sebou a veľkosťami vedľa seba (matica), použite INDEX s dvoma funkciami MATCH. S produktmi v A11:A13, veľkosťami S, M a L v B10:D10 a cenami v B11:D13:
=INDEX(B11:D13;MATCH(F2;A11:A13;0);MATCH(G2;B10:D10;0))
Prvá funkcia MATCH nájde riadok produktu, druhá stĺpec veľkosti. INDEX vráti bunku na ich priesečníku.
Ktorý spôsob zvoliť?
| Situácia | Najlepšia voľba |
|---|---|
| Jedna odpoveď s prehľadným hlásením, ak neexistuje | XLOOKUP so spojenými hodnotami |
| Súbor musí fungovať aj v starom Exceli | SUMIFS |
| Môže existovať viac zhôd | FILTER |
| Cenová tabuľka s veľkosťami ako hlavičkami stĺpcov | INDEX s dvoma funkciami MATCH |
Časté chyby
- #N/A, hoci kombinácia naozaj existuje: často ide o medzeru za hodnotou ("Boeket ") alebo o číslo uložené ako text. Pozrite VLOOKUP vracia #N/A: ako to vyriešiť.
- Rozsahy rôznej dĺžky: =XLOOKUP(F2;A2:A7;C2:C50) vráti #VALUE!. Všetky rozsahy by mali začínať aj končiť v rovnakom riadku.
- Celé stĺpce (A:A) v týchto vzorcoch spomaľujú veľký súbor, pretože Euro-Office potom spája milión riadkov. Použite pevný rozsah, napríklad A2:A500. Pozrite Ako zrýchliť pomalý zošit so vzorcami.
- Kolega s Excelom 2019 alebo starším uvidí pri XLOOKUP a FILTER chybu #NAME?.
