excelkurz.sk

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.

AB
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.

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])

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

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

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.

B7 =XLOOKUP(B6;B2:B4;A2:A4)
AB
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.

B7 =XLOOKUP(B6;A2:A4;B2:B4;"voľná")
AB
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 €.

B7 =XLOOKUP(B6;A2:A4;B2:B4;;-1)
AB
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.

Vyriešené: 0 z 5 Body: 0 Séria: 0 dní

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.

ABC
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.

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.

AB
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.

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.

AB
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.

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.

ABC
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.

Cvičenie 5: Cena podľa dvoch údajov

náročnejšia

E-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.

ABC
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.

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.

Súvisiace