VLOOKUP (funkcia)

Tip: Vyskúšajte novú funkciu XLOOKUP, vylepšenú verziu funkcie VLOOKUP, ktorá funguje v ľubovoľnom smere a predvolene vráti presné zhody, vďaka čomu sa používa jednoduchšie a pohodlnejšie ako predchádzajúca verzia.

Funkciu VLOOKUP použite vtedy, keď potrebujete nájsť položky v tabuľke alebo rozsahu podľa riadka. Môžete napríklad vyhľadať cenu automobilovej časti podľa čísla sú časti alebo nájsť meno zamestnanca na základe jeho ID zamestnanca.

V najjednoduchšej podobe funkcia VLOOKUP znamená:

=VLOOKUP(Čo chcete vyhľadať, kde ho chcete vyhľadať, číslo stĺpca v rozsahu obsahujúcom hodnotu, ktorá sa má vrátiť, vrátenie približnej alebo presnej zhody – označené ako 1/TRUE alebo 0/FALSE).

Váš prehliadač nepodporuje video. Nainštalujte si Microsoft Silverlight, Adobe Flash Player alebo Internet Explorer 9.

Tip: Tajomstvo funkcie VLOOKUP spočíva v usporiadaní údajov tak, aby hľadaná hodnota (Ovocie) bola naľavo od vrátenej hodnoty (Amount), ktorú chcete nájsť.

Funkciu VLOOKUP použite na vyhľadanie hodnoty v tabuľke.

Syntax

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

Príklad:

=VLOOKUP(A2;A10:C20;2;TRUE)
=VLOOKUP("Sýkora";B2:E7;2;FALSE)
=VLOOKUP(A2;'Podrobnosti o klientovi'! A:F,3;FALSE)

Názov argumentu	Popis
Hľadaná_hodnota (povinné)	Hodnota, ktorú chcete vyhľadať. Hodnota, ktorú chcete vyhľadať, musí byť v prvom stĺpci rozsahu buniek, ktorý zadáte v argumente *table_array* . Ak napríklad *pole tabuľky* zahŕňa bunky B2:D7, lookup_value musí byť v stĺpci B. *Hodnota argumentu hľadaná_hodnota* môže byť hodnotou alebo odkazom na bunku.
Pole_tabuľky (povinné)	Rozsah buniek, ktorý bude funkcia VLOOKUP prehľadávať na základe argumentu *hľadaná_hodnota* a vrátenej hodnoty. Môžete použiť pomenovaný rozsah alebo tabuľku a namiesto odkazov na bunky môžete v argumente použiť názvy. Prvý stĺpec v rozsahu buniek musí obsahovať *lookup_value*. Rozsah buniek musí obsahovať aj vrátenú hodnotu, ktorú chcete vyhľadať. Zistite, ako vybrať rozsah v hárku.
Číslo_indexu_stĺpca (povinné)	Číslo stĺpca (počnúc číslom 1 pre stĺpec *table_array* úplne vľavo), ktorý obsahuje vrátenú hodnotu.
Vyhľadávanie_rozsahu (voliteľné)	Logická hodnota, ktorá určuje, či má funkcia VLOOKUP vyhľadať úplnú alebo približnú zhodu: Približná zhoda – 1/TRUE predpokladá, že prvý stĺpec v tabuľke je zoradený číselne alebo abecedne a potom vyhľadá najbližšiu hodnotu. Ak neurčíte žiadnu metódu, použije sa táto ako predvolená. Príklad: =VLOOKUP(90;A1:B100;2;TRUE). Presná zhoda – 0/FALSE vyhľadá presnú hodnotu v prvom stĺpci. Príklad: =VLOOKUP("Kováč";A1:B100;2;FALSE).

Ako začať

Na zostavenie syntaxe funkcie VLOOKUP potrebujete tieto štyri informácie:

Hodnotu, ktorú chcete vyhľadať, tzv. hľadanú hodnotu.
Rozsah, v ktorom sa hľadaná hodnota nachádza. Nezabudnite na to, že aby funkcia VLOOKUP fungovala správne, hľadaná hodnota musí byť vždy v prvom stĺpci rozsahu. Ak sa hľadaná hodnota nachádza napríklad v bunke C2, rozsah by sa mal začínať stĺpcom C.
Číslo stĺpca v rozsahu, v ktorom sa nachádza vrátená hodnota. Ak napríklad zadáte rozsah B2:D11, B by ste mali počítať ako prvý stĺpec, C ako druhý a tak ďalej.
Voliteľne môžete zadať hodnotu TRUE, ak chcete približnú zhodu alebo hodnotu FALSE, ak chcete presnú zhodu vrátenej hodnoty. Ak nič nezadáte, predvolená hodnota bude vždy TRUE alebo približná zhoda.

Všetky uvedené informácie teraz spojte dohromady:

=VLOOKUP(vyhľadávaná hodnota; rozsah obsahujúci hľadanú hodnotu, číslo stĺpca v rozsahu obsahujúcom vrátenú hodnotu, približná zhoda (TRUE) alebo presná zhoda (FALSE)).

Príklady

Tu je niekoľko príkladov funkcie VLOOKUP:

Príklad č. 1

=VLOOKUP (B3;B2:E7;2;FALSE)

Funkcia VLOOKUP vyhľadá písmo Fontana v prvom stĺpci (stĺpci B) v table_array B2:E7 a vráti oliviera z druhého stĺpca (stĺpca C) table_array. Hodnota False vráti presnú zhodu.

Príklad č. 2

=VLOOKUP (102;A2:C7;2;FALSE)

Funkcia VLOOKUP vyhľadá presnú zhodu (FALSE) priezviska 102 (lookup_value) v druhom stĺpci (stĺpci B) v rozsahu A2:C7 a vráti hodnotu Fontana.

Príklad č. 3

=IF(VLOOKUP(103;A1:E7;2;FALSE)="Souse";"Umiestnené";"Nenašlo sa")

Funkcia IF skontroluje, či funkcia VLOOKUP vráti Sousu ako priezvisko zamestnanca, ktoré korešponduje s číslom 103 (lookup_value) v rozsahu A1:E7 (table_array). Keďže priezvisko zodpovedajúce 103 je Leal, podmienka IF je nepravdivá a nezobrazuje sa.

Príklad č. 4

=INT(YEARFRAC(DATE(2014;6;30);VLOOKUP(105;A2:E7;5;FLASE);1))

Funkcia VLOOKUP vyhľadá dátum narodenia zamestnanca zodpovedajúci 109 (lookup_value) v rozsahu A2:E7 (table_array) a vráti hodnotu 03/04/1955. Potom funkcia YEARFRAC odčíta tento dátum narodenia od 6.6.2010 a vráti hodnotu, ktorú iny potom skonvertuje na celé číslo 59.

Príklad č. 5

IF(ISNA(VLOOKUP(105;A2:E7;2;FLASE))=TRUE;"Zamestnanec sa nenašiel";VLOOKUP(105;A2:E7;2;FALSE))

Funkcia IF skontroluje, či funkcia VLOOKUP vráti hodnotu priezviska zo stĺpca B pre číslo 105 (lookup_value). Ak funkcia VLOOKUP nájde priezvisko, funkcia IF zobrazí priezvisko, v opačnom prípade funkcia IF vráti hodnotu Zamestnanec sa nenašiel. Funkcia ISNA zabezpečí, aby sa pri vrátení funkcie VLOOKUP #N/A namiesto hodnoty #N/A chyba nahradila položkou Zamestnanec.

V tomto príklade je vrátená hodnota Burke, čo je priezvisko zodpovedajúce 105.

Funkciu VLOOKUP môžete použiť na skombinovanie viacerých tabuliek do jednej, ak má jedna z tabuliek polia spoločné so všetkými ostatnými. Môže to byť užitočné najmä vtedy, ak potrebujete zdieľať zošit s ľuďmi, ktorí majú staršie verzie Excelu, ktoré nepodporujú funkcie údajov s viacerými tabuľkami ako zdrojmi údajov – kombináciou zdrojov do jednej tabuľky a zmenou zdroja údajov funkcie údajov na novú tabuľku možno funkciu údajov použiť v starších verziách Excelu (za predpokladu, že samotná funkcia údajov je podporovaná staršou verziou).

Hárok so stĺpcami, ktoré používajú funkciu VLOOKUP na získavanie údajov z iných tabuliek

Tu stĺpce A-F a H obsahujú hodnoty alebo vzorce, ktoré používajú iba hodnoty v hárku, a ostatné stĺpce používajú funkciu VLOOKUP a hodnoty stĺpca A (Kód klienta) a stĺpec B (Advokát) na získanie údajov z iných tabuliek.

Skopírujte tabuľku, ktorá obsahuje spoločné polia, do nového hárka a pomenujte ju.
Kliknutím na položku Nástroje údajov > údaje > vzťahy otvorte dialógové okno Správa vzťahov.
Pri každom uvedenom vzťahu si všimnite nasledovné:
- Pole, ktoré prepája tabuľky (uvedené v zátvorkách v dialógovom okne). Toto je lookup_value vzorca funkcie VLOOKUP.
- Názov súvisiacej vyhľadávacej tabuľky. Toto je table_array vo vzorci funkcie VLOOKUP.
- Pole (stĺpec) v súvisiacej vyhľadávacej tabuľke obsahujúce údaje, ktoré chcete mať v novom stĺpci. Tieto informácie sa nezobrazujú v dialógovom okne Správa vzťahov – budete si musieť pozrieť súvisiacu vyhľadávaciu tabuľku a zistiť, ktoré pole chcete načítať. Chcete si poznamenať číslo stĺpca (A=1) – toto je col_index_num vo vzorci.
Ak chcete pridať pole do novej tabuľky, zadajte vzorec funkcie VLOOKUP do prvého prázdneho stĺpca pomocou informácií, ktoré ste zhromaždili v kroku 3.

V našom príklade stĺpec G používa funkciu Advokát ( lookup_value) na získanie údajov Bill Rate zo štvrtého stĺpca (col_index_num = 4) z tabuľky hárka Advokáti, tblAttorneys ( table_array) so vzorcom =VLOOKUP([@Attorney],tbl_Attorneys;4;FALSE).

Vzorec môže použiť aj odkaz na bunku a odkaz na rozsah. V našom príklade by to bolo =VLOOKUP(A2;'Advokáti'! A:D,4,FALSE).
Pokračujte v pridávaní polí, kým nebudete mať všetky polia, ktoré potrebujete. Ak sa pokúšate pripraviť zošit obsahujúci funkcie údajov, ktoré používajú viacero tabuliek, zmeňte zdroj údajov funkcie údajov na novú tabuľku.

Problém	Kde sa stala chyba
Vrátená nesprávna hodnota	Ak má argument *vyhľadávanie_rozsahu* hodnotu TRUE alebo je hodnota vynechaná, musí byť prvý stĺpec zoradený v abecednom alebo číselnom poradí. Ak prvý stĺpec nie je zoradený, vrátená hodnota môže obsahovať niečo, čo neočakávate. Zoraďte prvý stĺpec alebo použite hodnotu FALSE pre presnú zhodu.
V bunke sa nachádza chyba #NEDOSTUPNÝ	Ak má argument *vyhľadávanie_rozsahu* hodnotu TRUE a ak je hodnota v argumente *vyhľadávaná_hodnota* menšia ako najmenšia hodnota v prvom stĺpci tabuľky argumentu *pole_tabuľky, zobrazí sa chyba #NEDOSTUPNÝ. Ak má argument vyhľadávanie_rozsahu* hodnotu FALSE, chybová hodnota #NEDOSTUPNÝ znamená, že presné číslo nebolo nájdené. Ďalšie informácie o odstraňovaní chyby #NEDOSTUPNÝ vo funkcii VLOOKUP nájdete v téme Odstránenie chyby #NEDOSTUPNÝ vo funkcii VLOOKUP.
Chyba typu #ODKAZ! v bunke	Ak *je col_index_num* väčší ako počet stĺpcov v *poli tabuľky*, zobrazí sa #REF! . Ďalšie informácie o riešení #REF! chyby vo funkcii VLOOKUP, pozrite si tému Odstránenie chyby #REF!.
#HODNOTA! v bunke	Ak *je table_array* menší ako 1, zobrazí sa #VALUE! . Ďalšie informácie o odstraňovaní chyby #HODNOTA! vo funkcii VLOOKUP nájdete v téme Odstránenie chyby #HODNOTA! vo funkcii VLOOKUP.
#NÁZOV? v bunke	Chybová hodnota #NÁZOV? zvyčajne znamená, že vo vzorci chýbajú úvodzovky. Ak chcete vyhľadať meno osoby, skontrolujte, či vo vzorci pred a za menom používate úvodzovky. Zadajte napríklad meno v tvare "Sýkora" vo funkcii =VLOOKUP("Fontana";B2:E7;2;FALSE). Ďalšie informácie nájdete v téme Odstránenie chyby #NAME!.
Chyba #PRESAHOVANIE! v bunke	Táto konkrétna chyba #SPILL! zvyčajne znamená, že vzorec sa spolieha na implicitný prienik pre hľadanú hodnotu a ako odkaz používa celý stĺpec. Príklad: =VLOOKUP(A:A;A:C;2;FALSE). Tento problém môžete vyriešiť ukotvením vyhľadávacieho odkazu s operátorom @ takto: =VLOOKUP(@A:A;A:C;2;FALSE). Prípadne môžete použiť tradičnú metódu VLOOKUP a odkazovať na jednu bunku namiesto celého stĺpca: =VLOOKUP(A2;A:C;2;FALSE).

Postup	Dôvody
Pre argument vyhľadávanie_rozsahu používajte absolútne odkazy	Použitie absolútnych odkazov umožňuje vyplniť vzorec tak, že sa bude vždy zobrazovať v rovnakom rozsahu vyhľadávania. Ďalšie informácie o absolútnych odkazoch na bunky.
Číselné hodnoty alebo hodnoty dátumov neukladajte ako textové hodnoty.	Pri vyhľadávaní číselných alebo dátumových hodnôt nesmú byť údaje v prvom stĺpci argumentu *pole_tabuľky* uložené ako textové hodnoty. V opačnom prípade by funkcia VLOOKUP mohla vrátiť nesprávne alebo neočakávané hodnoty.
Zoraďte prvý stĺpec	Prvý stĺpec tabuľky argumentu *pole_tabuľky* zoraďte ešte pred použitím funkcie VLOOKUP, a to v prípade, ak má argument vyhľadávanie_rozsahu hodnotu TRUE.
Používajte zástupné znaky	Ak *range_lookup* má hodnotu FALSE a *lookup_value* je text, môžete v *lookup_value* použiť zástupné znaky – otáznik (?) a hviezdičku (). Otáznik predstavuje ľubovoľný jeden znak. Hviezdička predstavuje ľubovoľnú sekvenciu znakov. Ak chcete vyhľadať skutočný otáznik alebo hviezdičku, zadajte pred znakom vlnovku (~). Napríklad funkcia =VLOOKUP("Fontan?";B2:E7;2;FALSE) vyhľadá všetky inštancie fontany* s posledným písmenom, ktoré sa môže líšiť.
Skontrolujte, či údaje neobsahujú chybné znaky.	Pri vyhľadávaní textových hodnôt v prvom stĺpci nesmú údaje v prvom stĺpci obsahovať žiadne úvodné a koncové medzery, nekonzistentne použité rovné (' alebo ") a oblé (‘ alebo “) úvodzovky ani znaky, ktoré sa nedajú tlačiť. V takýchto prípadoch by funkcia VLOOKUP mohla vrátiť neočakávané hodnoty. Ak chcete získať presné výsledky, treba odstrániť koncové medzery v bunke za hodnotami tabuliek. Skúste použiť funkcie CLEAN alebo TRIM.