excelkurz.sk

Funkcia VLOOKUP v Exceli: vyhľadanie v tabuľke

VLOOKUP nájde riadok podľa hodnoty v prvom stĺpci tabuľky a vráti údaj z iného stĺpca toho istého riadku. Naučíš sa ju napísať, vybrať správne číslo stĺpca a opraviť chybu #N/A.

Vyskúšaj si to: Cena nápoja

Do bunky B8 napíš =VLOOKUP(B7;A2:B5;2;FALSE) a stlač Enter. Excel nájde nápoj z B7 v prvom stĺpci a vráti jeho cenu z druhého stĺpca.

AB
1 Nápoj Cena (€)
2 Espresso 1,8
3 Cappuccino 2,6
4 Latte 2,9
5 Čaj 2,2
6
7 Hľadám: Latte
8 Cena:

Klikni na bunku a jej adresa sa vloží do vzorca.

Ako funguje funkcia VLOOKUP

=VLOOKUP(vyhľadávaná_hodnota; pole_tabuľky; číslo_indexu_stĺpca; [vyhľadávanie_rozsahu])

Funkcia sa aj v slovenskom Exceli volá VLOOKUP a argumenty oddeľuješ bodkočiarkou. Keď hľadáš mená, kódy alebo čísla faktúr, chceš takmer vždy presnú zhodu, teda FALSE na konci.

Presná a približná zhoda

S FALSE nájde VLOOKUP len riadok, v ktorom sa hodnota zhoduje presne. Keď taký riadok nenájde, vráti chybu #N/A. Pri presnej zhode môžeš v texte použiť aj zástupné znaky: hviezdička * zastúpi ľubovoľný text, otáznik ? jeden znak.

S TRUE nájde VLOOKUP najväčšiu hodnotu, ktorá je menšia alebo rovná hľadanej. To sa hodí na pásma: bonus podľa tržby, zľava podľa množstva, známka podľa bodov. Prvý stĺpec tabuľky však musí byť zoradený od najmenšej hodnoty, inak môže VLOOKUP vrátiť nesprávny výsledok a chybu neohlási.

Čo VLOOKUP nevie

VLOOKUP hľadá len v prvom stĺpci tabuľky a výsledok berie zo stĺpca napravo od neho. Keď je výsledok naľavo, použi XLOOKUP. Číslo stĺpca je vo vzorci napísané pevne. Keď do tabuľky vložíš nový stĺpec, vzorec môže začať vracať údaje z iného stĺpca.

Príklady

Presné vyhľadanie

Servis eviduje zákazníkov podľa čísla zmluvy. V B9 je meno zákazníka so zmluvou z B8.

B9 =VLOOKUP(B8;A2:C5;2;FALSE)
ABC
1 Zmluva Zákazník Mesto
2 Z-2041 Peter Lukáč Poprad
3 Z-2057 Jana Vrbová Senica
4 Z-2032 Milan Rusnák Levice
5 Z-2068 Eva Zelená Zvolen
6
7
8 Zmluva: Z-2057
9 Zákazník: Jana Vrbová

Pásma: známka podľa bodov

Učiteľka má stupnicu: od 90 bodov jednotka, od 80 dvojka a tak ďalej. Prvý stĺpec je zoradený od najmenšieho čísla, preto funguje približná zhoda. Pre 83 bodov VLOOKUP nájde riadok od 80 a vráti známku 2.

B9 =VLOOKUP(B8;A2:B6;2;TRUE)
AB
1 Body od Známka
2 0 5
3 50 4
4 65 3
5 80 2
6 90 1
7
8 Body: 83
9 Známka: 2

Vlastný text namiesto #N/A

Keď hľadaná hodnota v tabuľke nie je, VLOOKUP vráti #N/A. Funkcia IFERROR namiesto chyby ukáže text, ktorý zadáš.

B7 =IFERROR(VLOOKUP(B6;A2:B4;2;FALSE);"nie je v ponuke")
AB
1 Tovar Sklad (ks)
2 Stolička 24
3 Stôl 6
4 Lampa 13
5
6 Hľadám: Polica
7 Na sklade: nie je v ponuke

Časté chyby a čo znamenajú

Chyba #N/A

Hľadaná hodnota sa v prvom stĺpci tabuľky nenašla. Najčastejšie preto, že:

Chyba #REF!

Číslo stĺpca je väčšie, ako má tabuľka stĺpcov. Stĺpce sa počítajú od začiatku tabuľky, nie od stĺpca A. Tabuľka B2:D10 má tri stĺpce, aj keď v hárku končí stĺpcom D.

Chyba #VALUE!

Číslo stĺpca je menšie ako 1. Prvý stĺpec tabuľky má číslo 1, nula ani záporné číslo nefungujú.

Nesprávny výsledok bez chyby

Na konci chýba FALSE a prvý stĺpec nie je zoradený. VLOOKUP vtedy hľadá približne a vráti hodnotu z nesprávneho riadku. Pri menách a kódoch dopíš FALSE.

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, takže odkaz priamo na výsledok neprejde.

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

Cvičenie 1: Klapka zamestnanca

ľahká

V bunke B8 je meno zamestnanca. Do B9 napíš vzorec, ktorý v tabuľke nad ním nájde jeho klapku.

ABC
1 Meno Oddelenie Klapka
2 Novák Účtovníctvo 214
3 Horváthová Predaj 305
4 Kováč Sklad 118
5 Baláž IT 402
6 Szabová Predaj 311
7
8 Meno: Kováč
9 Klapka:

Klikni na bunku a jej adresa sa vloží do vzorca.

Cvičenie 2: Cena podľa kódu

ľahká

Tabuľka tovaru začína až v stĺpci B, v stĺpci A je len poradové číslo. Do B9 napíš vzorec, ktorý podľa kódu v B8 nájde cenu.

ABCD
1 Č. Kód Tovar Cena (€)
2 1 M-101 Mlieko 1,15
3 2 C-220 Chlieb 2,1
4 3 S-310 Syr 3,45
5 4 J-405 Jogurt 0,79
6 5 K-512 Káva 6,9
7
8 Kód: S-310
9 Cena:

Klikni na bunku a jej adresa sa vloží do vzorca.

Cvičenie 3: Bonus podľa tržby

stredná

Obchodník dostane bonus podľa mesačnej tržby: od 5 000 € je to 150 €, od 10 000 € 400 € a od 20 000 € 1 000 €. Do B8 napíš vzorec, ktorý nájde bonus pre tržbu v B7.

AB
1 Tržba od (€) Bonus (€)
2 0 0
3 5000 150
4 10000 400
5 20000 1000
6
7 Tržba: 12750
8 Bonus:

Klikni na bunku a jej adresa sa vloží do vzorca.

Cvičenie 4: Cena objednávky

stredná

Škola objednáva pomôcky. Do B10 napíš vzorec, ktorý nájde cenu tovaru z B8 a vynásobí ju počtom kusov v B9.

AB
1 Tovar Cena (€)
2 Zošit A4 1,2
3 Pero 0,8
4 Pravítko 1,5
5 Lepidlo 2,3
6 Nožnice 3,9
7
8 Tovar: Lepidlo
9 Kusov: 12
10 Spolu (€):

Klikni na bunku a jej adresa sa vloží do vzorca.

Cvičenie 5: Ceny do objednávky

náročnejšia

Vľavo je cenník, vpravo objednávka. Do D2 napíš vzorec, ktorý nájde cenu tovaru z C2. Vzorec sa potom skopíruje do D3 až D5, preto musí fungovať aj tam.

ABCD
1 Tovar Cena (€) Objednané Cena (€)
2 Káva 6,9 Mlieko
3 Čaj 2,4 Káva
4 Cukor 1,1 Sušienky
5 Mlieko 1,15 Čaj
6 Sušienky 1,8

Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.

Otázky a odpovede

Prečo VLOOKUP nefunguje?

Pri chybe #N/A skontroluj, či tabuľka začína stĺpcom, v ktorom hľadáš, a či sa hodnoty nelíšia medzerou alebo tým, že jedna je číslo a druhá text. Keď vzorec vracia nesprávny údaj bez chyby, chýba na konci FALSE. Keď vracia údaj zo zlého stĺpca, prepočítaj číslo stĺpca od začiatku tabuľky.

Ako sa píše VLOOKUP po slovensky?

Aj v slovenskom Exceli sa funkcia volá VLOOKUP. České SVYHLEDAT v ňom nefunguje. Rozdiel je len v oddeľovači: argumenty oddeľuje bodkočiarka, napríklad =VLOOKUP(B8;A2:C6;3;FALSE).

Ako použiť VLOOKUP medzi dvoma hárkami?

Pred rozsah tabuľky napíš názov hárka a výkričník: =VLOOKUP(A2;Cenník!$A$2:$B$100;2;FALSE). Keď je v názve hárka medzera, daj ho do apostrofov: 'Cenník 2026'!$A$2:$B$100. Najjednoduchšie je pri písaní vzorca prepnúť na druhý hárok a tabuľku označiť myšou.

Aký je rozdiel medzi VLOOKUP a XLOOKUP?

XLOOKUP je novšia funkcia. Výsledok vráti aj zo stĺpca naľavo, netreba v nej počítať stĺpce a presná zhoda je predvolená. Nie je však v Exceli 2019 a starších verziách. Viac na stránke Funkcia XLOOKUP.

Súvisiace