Funkcia XLOOKUP v Exceli: vyhľadanie doľava aj doprava
XLOOKUP nájde hodnotu v jednom stĺpci a vráti údaj z iného stĺpca, aj keď je naľavo. Naučíš sa jej argumenty, vlastný text namiesto chyby #N/A a hľadanie v pásmach či od konca tabuľky.
Vyskúšaj si to: Kraj podľa mesta
Do bunky B8 napíš =XLOOKUP(B7;A2:A5;B2:B5) a stlač Enter. Excel nájde mesto z B7 v stĺpci A a vráti kraj z rovnakého riadku stĺpca B.
| A | B | |
|---|---|---|
| 1 | Mesto | Kraj |
| 2 | Prešov | Prešovský |
| 3 | Trenčín | Trenčiansky |
| 4 | Banská Bystrica | Banskobystrický |
| 5 | Trnava | Trnavský |
| 6 | ||
| 7 | Mesto: | Trenčín |
| 8 | Kraj: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =XLOOKUP(B7;A2:A5;B2:B5)
XLOOKUP hľadá B7 v poli A2:A5. Keď mesto nájde, vráti hodnotu z rovnakého riadku poľa B2:B5.
Ako funguje funkcia XLOOKUP
=XLOOKUP(vyhľadávaná_hodnota; pole_vyhľadávania; pole_vrátenia; [ak_sa_nenájde]; [režim_zhody]; [režim_vyhľadávania])
- vyhľadávaná_hodnota: čo hľadáš, najčastejšie odkaz na bunku.
- pole_vyhľadávania: stĺpec (alebo riadok), v ktorom hľadáš.
- pole_vrátenia: stĺpec, z ktorého sa vráti výsledok. Musí mať rovnaký počet riadkov ako pole vyhľadávania a môže byť naľavo aj napravo od neho.
- ak_sa_nenájde: čo sa ukáže, keď sa hodnota nenájde, napríklad
"nie je v zozname". Bez neho vráti XLOOKUP chybu #N/A. - režim_zhody a režim_vyhľadávania: nepovinné, opísané nižšie.
Prvé tri argumenty sú povinné. Keď chceš vynechať štvrtý a zadať piaty, nechaj medzi bodkočiarkami
prázdne miesto: =XLOOKUP(B7;A2:A5;B2:B5;;-1).
Režim zhody a režim vyhľadávania
- 0: presná zhoda. Je predvolená, takže ju písať netreba.
- -1: presná zhoda, inak najbližšia menšia hodnota (pásma „od“).
- 1: presná zhoda, inak najbližšia väčšia hodnota.
- 2: zhoda so zástupnými znakmi * a ?.
Režim vyhľadávania 1 (predvolený) hľadá od prvej položky, -1 od poslednej. Hodnoty 2 a -2 hľadajú binárne a potrebujú zoradené údaje.
Ktorý Excel má XLOOKUP
XLOOKUP je v Exceli pre Microsoft 365, v Exceli 2021 a 2024 a v Exceli na webe. V Exceli 2019, 2016 a starších verziách nie je, vzorec tam vráti chybu #NAME?. Ak súbor otvárajú ľudia so starším Excelom, použi VLOOKUP.
XLOOKUP verzus VLOOKUP
- XLOOKUP vráti výsledok aj zo stĺpca naľavo, VLOOKUP len napravo.
- V XLOOKUP netreba počítať stĺpce. Vložený stĺpec v tabuľke vzorec nerozbije.
- Presná zhoda je predvolená, pri VLOOKUP treba písať FALSE.
- Vlastný text namiesto #N/A zvládne XLOOKUP bez ďalšej funkcie, VLOOKUP potrebuje IFERROR.
- XLOOKUP vie hľadať od konca tabuľky a pásma nemusia byť zoradené.
Príklady
Výsledok naľavo
Kód tovaru je v stĺpci B, názov v stĺpci A. XLOOKUP hľadá v B a vráti názov z A.
=XLOOKUP(B6;B2:B4;A2:A4) | A | B | |
|---|---|---|
| 1 | Tovar | Kód |
| 2 | Hrnček | D-17 |
| 3 | Tanier | D-04 |
| 4 | Misa | D-22 |
| 5 | ||
| 6 | Kód: | D-04 |
| 7 | Tovar: | Tanier |
Text, keď sa nič nenájde
Štvrtý argument nahradí chybu #N/A vlastným textom.
=XLOOKUP(B6;A2:A4;B2:B4;"voľná") | A | B | |
|---|---|---|
| 1 | Izba | Hosť |
| 2 | 101 | Ondrejka |
| 3 | 102 | Kollár |
| 4 | 104 | Šuster |
| 5 | ||
| 6 | Izba: | 103 |
| 7 | Hosť: | voľná |
Pásma bez zoradenia
Režim zhody -1 nájde najbližšiu menšiu hodnotu, takže zvládne pásma ako VLOOKUP s TRUE. Tabuľka pritom nemusí byť zoradená. Pre objednávku za 260 € je to pásmo od 200 €.
=XLOOKUP(B6;A2:A4;B2:B4;;-1) | A | B | |
|---|---|---|
| 1 | Objednávka od (€) | Doprava (€) |
| 2 | 200 | 0,00 |
| 3 | 0 | 4,90 |
| 4 | 80 | 2,90 |
| 5 | ||
| 6 | Objednávka: | 260 |
| 7 | Doprava: | 0,00 |
Časté chyby a čo znamenajú
Chyba #N/A
Hodnota sa v poli vyhľadávania nenašla. Skontroluj, či hľadáš v správnom stĺpci, či sa hodnoty nelíšia medzerou navyše a či nie je jedna číslo a druhá text. Ak je to v poriadku a hodnota tam naozaj nie je, doplň štvrtý argument s textom.
Chyba #VALUE!
Pole vyhľadávania a pole vrátenia majú rôzny počet riadkov, napríklad A2:A10 a B2:B9. Rozsahy musia začínať aj končiť v rovnakých riadkoch.
Chyba #NAME?
Excel funkciu XLOOKUP nepozná. Buď je v názve preklep, alebo máš Excel 2019 či starší.
Vymenené polia
Druhý argument je stĺpec, v ktorom hľadáš, tretí je stĺpec s výsledkom. Keď ich vymeníš, XLOOKUP hľadá v nesprávnom stĺpci a vráti #N/A.
Cvičenia
Päť úloh od ľahkých po náročnejšie. Vzorec píš do vyznačenej bunky, kontrolu spustíš klávesom Enter alebo tlačidlom Skontrolovať. Kontrola vzorec vyskúša aj s inými hľadanými hodnotami.
Cvičenie 1: Meno podľa osobného čísla
ľahkáMená sú v stĺpci A, osobné čísla v stĺpci B. Do B9 napíš vzorec, ktorý podľa osobného čísla v B8 nájde meno. VLOOKUP to nedokáže, lebo meno je naľavo od čísla.
| A | B | C | |
|---|---|---|---|
| 1 | Meno | Os. číslo | Oddelenie |
| 2 | Lucia Kráľová | 1042 | Predaj |
| 3 | Marek Hudec | 1077 | Sklad |
| 4 | Ivana Molnárová | 1015 | Účtovníctvo |
| 5 | Tomáš Benko | 1103 | IT |
| 6 | Petra Šimková | 1061 | Predaj |
| 7 | |||
| 8 | Os. číslo: | 1015 | |
| 9 | Meno: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =XLOOKUP(B8;B2:B6;A2:A6)
=XLOOKUP(B8;B2:B6;A2:A6) hľadá číslo v stĺpci B a vráti meno zo stĺpca A. Pri XLOOKUP na rozdiel od VLOOKUP nezáleží na tom, na ktorej strane je výsledok.
Cvičenie 2: Keď sa nenájde
ľahkáPredajňa ovocia hľadá cenu podľa názvu v B8. Keď ovocie v zozname nie je, má sa namiesto chyby ukázať text „nie je v ponuke“. Vzorec napíš do B9.
| A | B | |
|---|---|---|
| 1 | Ovocie | Cena (€/kg) |
| 2 | Jablká | 1,19 |
| 3 | Banány | 1,49 |
| 4 | Hrušky | 2,49 |
| 5 | Pomaranče | 1,89 |
| 6 | Slivky | 2,1 |
| 7 | ||
| 8 | Hľadám: | Hrušky |
| 9 | Cena: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =XLOOKUP(B8;A2:A6;B2:B6;"nie je v ponuke")
Štvrtý argument XLOOKUP sa použije, keď sa ovocie nenájde. Bez neho by vzorec pri kiwi vrátil chybu #N/A.
Cvičenie 3: Poštovné podľa hmotnosti
strednáPoštovné závisí od hmotnosti balíka: pod 2 kg 3,20 €, od 2 kg 4,50 €, od 5 kg 6,90 € a od 10 kg 9,80 €. Do B8 napíš vzorec, ktorý nájde poštovné pre hmotnosť v B7.
| A | B | |
|---|---|---|
| 1 | Od (kg) | Poštovné (€) |
| 2 | 0 | 3,2 |
| 3 | 2 | 4,5 |
| 4 | 5 | 6,9 |
| 5 | 10 | 9,8 |
| 6 | ||
| 7 | Hmotnosť: | 3,4 |
| 8 | Poštovné: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =XLOOKUP(B7;A2:A5;B2:B5;;-1)
Režim zhody -1 nájde presnú hmotnosť, a keď taká nie je, najbližšiu menšiu hranicu. Pre 3,4 kg je to riadok od 2 kg. Na rozdiel od VLOOKUP nemusí byť tabuľka zoradená.
Cvičenie 4: Posledná výpožička
strednáKnižnica v Martine si zapisuje výpožičky. Do B10 napíš vzorec, ktorý zistí, kto si knihu z B9 požičal naposledy.
| A | B | C | |
|---|---|---|---|
| 1 | Dátum | Kniha | Čitateľ |
| 2 | 3. 11. | Atlas sveta | Ema |
| 3 | 5. 11. | Kuchárka | Samuel |
| 4 | 8. 11. | Atlas sveta | Lukáš |
| 5 | 10. 11. | Kuchárka | Nina |
| 6 | 12. 11. | Atlas sveta | Tereza |
| 7 | |||
| 8 | |||
| 9 | Kniha: | Atlas sveta | |
| 10 | Naposledy: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =XLOOKUP(B9;B2:B6;C2:C6;;0;-1)
Režim vyhľadávania -1 prechádza tabuľku od posledného riadku, takže nájde poslednú výpožičku knihy. Bez neho XLOOKUP vráti prvú zhodu zhora.
Cvičenie 5: Cena podľa dvoch údajov
náročnejšiaE-shop predáva tričká a mikiny v rôznych veľkostiach. Do B10 napíš vzorec, ktorý nájde cenu pre produkt z B8 a veľkosť z B9.
| A | B | C | |
|---|---|---|---|
| 1 | Produkt | Veľkosť | Cena (€) |
| 2 | Tričko | S | 9,9 |
| 3 | Tričko | M | 10,9 |
| 4 | Tričko | L | 11,9 |
| 5 | Mikina | M | 24,9 |
| 6 | Mikina | L | 26,9 |
| 7 | |||
| 8 | Produkt: | Mikina | |
| 9 | Veľkosť: | L | |
| 10 | Cena: |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =XLOOKUP(B8&B9;A2:A6&B2:B6;C2:C6)
B8&B9 vytvorí text „MikinaL“ a A2:A6&B2:B6 spojí produkt a veľkosť v každom riadku tabuľky. XLOOKUP potom hľadá tento spojený text. Vzorec funguje v Exceli 365, 2021 a novšom.
Otázky a odpovede
Je lepší VLOOKUP alebo XLOOKUP?
Ak máš Excel pre Microsoft 365 alebo Excel 2021 či novší, funkcia XLOOKUP je pohodlnejšia a menej náchylná na chyby. VLOOKUP sa oplatí poznať, lebo funguje v každej verzii a je v mnohých starších súboroch.
Prečo XLOOKUP vracia #NAME?
Najčastejšie preto, že Excel je starší ako verzia 2021 a funkciu nepozná. Over aj, či je názov napísaný správne.
Dá sa v XLOOKUP hľadať podľa dvoch údajov?
Áno, spoj ich znakom &: =XLOOKUP(B8&B9;A2:A6&B2:B6;C2:C6) hľadá kombináciu produktu
a veľkosti. Vyskúšaš si to v piatom cvičení.
Vie XLOOKUP vrátiť viac stĺpcov naraz?
Áno. Keď je pole vrátenia širšie, napríklad B2:D10, XLOOKUP vráti všetky tri hodnoty z nájdeného riadku a výsledok presiahne do susedných buniek. Bunky napravo od vzorca musia byť prázdne.