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.
| A | B | |
|---|---|---|
| 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.
Riešenie: =VLOOKUP(B7;A2:B5;2;FALSE)
VLOOKUP hľadá hodnotu z B7 v prvom stĺpci tabuľky A2:B5. V nájdenom riadku vráti hodnotu z 2. stĺpca tabuľky, teda cenu. FALSE znamená presnú zhodu.
Ako funguje funkcia VLOOKUP
=VLOOKUP(vyhľadávaná_hodnota; pole_tabuľky; číslo_indexu_stĺpca; [vyhľadávanie_rozsahu])
- vyhľadávaná_hodnota: čo hľadáš, napríklad meno alebo kód tovaru. Najčastejšie je to odkaz na bunku.
- pole_tabuľky: rozsah, v ktorom VLOOKUP hľadá. Hodnotu hľadá vždy v jeho prvom stĺpci.
- číslo_indexu_stĺpca: z ktorého stĺpca tabuľky vrátiť výsledok. Počíta sa od prvého stĺpca tabuľky, ten má číslo 1.
- vyhľadávanie_rozsahu: FALSE (alebo 0) hľadá presnú zhodu, TRUE (alebo 1) približnú. Keď ho vynecháš, platí TRUE.
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.
=VLOOKUP(B8;A2:C5;2;FALSE) | A | B | C | |
|---|---|---|---|
| 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.
=VLOOKUP(B8;A2:B6;2;TRUE) | A | B | |
|---|---|---|
| 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áš.
=IFERROR(VLOOKUP(B6;A2:B4;2;FALSE);"nie je v ponuke") | A | B | |
|---|---|---|
| 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:
- tabuľka nezačína stĺpcom, v ktorom hľadáš (rozsah začína o stĺpec skôr alebo neskôr);
- hodnota sa líši medzerou navyše na konci, ktorú v bunke nevidno;
- v jednej tabuľke je číslo uložené ako číslo a v druhej ako text;
- vzorec sa skopíroval a tabuľka nemala znaky $, takže sa posunula nadol.
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.
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.
| A | B | C | |
|---|---|---|---|
| 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.
Riešenie: =VLOOKUP(B8;A2:C6;3;FALSE)
=VLOOKUP(B8;A2:C6;3;FALSE) nájde meno v prvom stĺpci tabuľky a vráti hodnotu z jej tretieho stĺpca. Hlavičku A1:C1 do tabuľky dávať netreba.
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.
| A | B | C | D | |
|---|---|---|---|---|
| 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.
Riešenie: =VLOOKUP(B8;B2:D6;3;FALSE)
Tabuľka B2:D6 začína kódmi, takže VLOOKUP hľadá kód v stĺpci B. Cena je v treťom stĺpci tabuľky, hoci v hárku je to stĺpec D.
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.
| A | B | |
|---|---|---|
| 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.
Riešenie: =VLOOKUP(B7;A2:B5;2;TRUE)
S TRUE (alebo bez štvrtého argumentu) VLOOKUP nájde posledné pásmo, ktorého začiatok je menší alebo rovný tržbe. Pre 12 750 € je to riadok od 10 000 € a bonus 400 €. Prvý stĺpec musí byť zoradený od najmenšej hodnoty.
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.
| A | B | |
|---|---|---|
| 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.
Riešenie: =VLOOKUP(B8;A2:B6;2;FALSE)*B9
VLOOKUP vráti cenu jedného kusu (2,30 €) a *B9 ju vynásobí počtom kusov. Rovnako funguje aj =B9*VLOOKUP(B8;A2:B6;2;FALSE).
Cvičenie 5: Ceny do objednávky
náročnejšiaVľ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.
| A | B | C | D | |
|---|---|---|---|---|
| 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í.
Riešenie: =VLOOKUP(C2;$A$2:$B$6;2;FALSE)
V =VLOOKUP(C2;$A$2:$B$6;2;FALSE) sa po skopírovaní mení len C2 na C3, C4, C5. Cenník $A$2:$B$6 ostáva rovnaký. Bez $ by sa posunul nadol a horné riadky cenníka by vypadli.
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.