Najpopulárnejšie z vyhľadávacích funkcií Excelu 2016 sú funkcie HLOOKUP (pre horizontálne vyhľadávanie) a VLOOKUP (pre vertikálne vyhľadávanie). Tieto funkcie sa nachádzajú v rozbaľovacej ponuke Lookup & Reference na karte Vzorce na páse s nástrojmi, ako aj v kategórii Lookup & Reference v dialógovom okne Vložiť funkciu. Sú súčasťou výkonnej skupiny funkcií, ktoré dokážu vrátiť hodnoty tak, že ich vyhľadajú v dátových tabuľkách.
Funkcia VLOOKUP prehľadáva vertikálne (zhora nadol) stĺpec úplne vľavo vo vyhľadávacej tabuľke, kým program nenájde hodnotu, ktorá sa zhoduje s hodnotou, ktorú hľadáte, alebo ju presahuje. Funkcia HLOOKUP prehľadáva vodorovne (zľava doprava) najvrchnejší riadok vyhľadávacej tabuľky, kým nenájde hodnotu, ktorá sa zhoduje s hodnotou, ktorú hľadáte, alebo ju presahuje.
Funkcia VLOOKUP používa nasledujúcu syntax:
VLOOKUP(vyhľadávacia_hodnota,pole_tabuľky,číslo_indexu stĺpca,[vyhľadávanie_rozsahu])
Funkcia HLOOKUP má takmer identickú syntax:
HLOOKUP(vyhľadávacia_hodnota,pole_tabuľky,index_riadku,[vyhľadávanie_rozsahu])
V oboch funkciách je argument lookup_value hodnotou, ktorú chcete vyhľadať vo vyhľadávacej tabuľke, a table_array je rozsah buniek alebo názov vyhľadávacej tabuľky, ktorý obsahuje hodnotu na vyhľadanie a súvisiacu hodnotu, ktorá sa má vrátiť.
Argument col_index_num označuje stĺpec vyhľadávacej tabuľky obsahujúci hodnoty, ktoré vracia funkcia VLOOKUP na základe porovnávania hodnoty argumentu lookup_value s hodnotami v argumente table_array. Určíte col_index_num počítanie argumentov, koľko stĺpcov táto kolóna je cez napravo od prvého stĺpca vertikálne tabuľky vyhľadávania a zahrnúť prvý stĺpec tabuľky vyhľadávania v tomto počte.
Argument row_index_num označuje riadok obsahujúci hodnoty vrátené funkciou HLOOKUP v horizontálnej tabuľke. Určíte row_index_num argumentu počítaním koľko riadkov nadol tomto riadku je od horného riadku tabuľky horizontálne vyhľadávania. Do tohto počtu opäť zahrniete horný riadok vyhľadávacej tabuľky.
Pri zadávaní argumentov col_index_num alebo row_index_num vo funkciách VLOOKUP a HLOOKUP nesmie zadaná hodnota prekročiť celkový počet stĺpcov alebo riadkov vo vyhľadávacej tabuľke.
Voliteľný argument typ argumentu v oboch VLOOKUP a VVYHLEDAT je logický true alebo false, ktorá určuje, či chcete Excel nájsť presný alebo približný zápas pre lookup_value v table_array. Ak zadáte hodnotu PRAVDA alebo vynechať argument typ argument funkcie VLOOKUP alebo VVYHLEDAT, Excel zistí približnú zhodu. Keď zadáte FALSE ako argument range_lookup , Excel nájde iba presné zhody.
Hľadanie približných zhôd sa týka iba vtedy, keď hľadáte číselné položky (namiesto textu) v prvom stĺpci alebo riadku vertikálnej alebo horizontálnej vyhľadávacej tabuľky. Keď Excel nenájde presnú zhodu v tomto vyhľadávacom stĺpci alebo riadku, nájde ďalšiu najvyššiu hodnotu, ktorá nepresahuje argument lookup_value, a potom vráti hodnotu v stĺpci alebo riadku určenom argumentmi col_index_num alebo row_index_num .
Pri používaní funkcií VLOOKUP a HLOOKUP musia byť textové alebo číselné položky v stĺpci alebo riadku vyhľadávania (t. j. v ľavom stĺpci vertikálnej vyhľadávacej tabuľky alebo v hornom riadku horizontálnej vyhľadávacej tabuľky) jedinečné. Tieto položky musia byť tiež usporiadané alebo zoradené vo vzostupnom poradí; to znamená abecedné poradie pre textové položky a od najnižšieho po najvyššie poradie pre číselné položky.
Obrázok ukazuje príklad použitia funkcie VLOOKUP na vrátenie 15% alebo 20% prepitného z tabuľky sprepitného v závislosti od súčtu šeku pred zdanením. Bunka F3 obsahuje funkciu VLOOKUP:
=VLOOKUP(Pretax_Total,Tip_Table,IF(Tip_Percentage=0,15;2,3))
Tento vzorec vráti sumu prepitného na základe percenta prepitného v bunke F1 a sumy pred zdanením šeku v bunke F2.
Použitie funkcie VLOOKUP na vrátenie sumy prepitného, ktoré sa má pridať z vyhľadávacej tabuľky.
Ak chcete použiť túto tabuľku sprepitného, zadajte percento prepitného (15 % alebo 20 %) do bunky F1 (s názvom Tip_Percentage) a sumu šeku pred zdanením do bunky F2 (s názvom Pretax_Total). Excel potom vyhľadá hodnotu, ktorú zadáte do bunky Pretax_Total v prvom stĺpci vyhľadávacej tabuľky, ktorá obsahuje rozsah buniek A2:C101 a má názov Tip_Table.
Excel potom posunie hodnoty v prvom stĺpci tabuľky Tip_Table nadol, kým nenájde zhodu, načo program použije argument col_index_num vo funkcii VLOOKUP na určenie sumy prepitného z daného riadku tabuľky, ktorá sa má vrátiť do bunky F3. Ak Excel zistí, že hodnota zadaná v bunke Pretax_Total (16,50 USD v tomto príklade) sa presne nezhoduje s jednou z hodnôt v prvom stĺpci tabuľky Tip_Table, program pokračuje v prehľadávaní porovnávacieho rozsahu, kým nenarazí na prvú hodnotu, ktorá presahuje celková suma pred zdanením (v tomto príklade 17,00 v bunke A19). Excel sa potom presunie späť na predchádzajúci riadok v tabuľke a vráti hodnotu v stĺpci, ktorá sa zhoduje s argumentom col_index_num funkcie VLOOKUP. (Je to preto, že voliteľné vyhľadávanie rozsahuargument bol z funkcie vynechaný.)
Všimnite si, že príklad tabuľky tipov na obrázku používa funkciu IF na určenie argumentu col_index_num pre funkciu VLOOKUP v bunke F3. Funkcia IF určuje číslo stĺpca, ktorý sa má použiť v tabuľke sprepitného, prirovnaním percenta zadaného v Tip_Percentage (bunka F1) s 0,15. Ak sa zhodujú, funkcia vráti 2 ako argument col_index_num a funkcia VLOOKUP vráti hodnotu z druhého stĺpca (15 % stĺpec B) v rozsahu Tabuľka tipov. V opačnom prípade funkcia IF vráti hodnotu 3 ako argument col_index_num a funkcia VLOOKUP vráti hodnotu z tretieho stĺpca (20 % stĺpec C) v rozsahu Tabuľka tipov.
Nasledujúci obrázok zobrazuje príklad, ktorý používa funkciu HLOOKUP na vyhľadanie ceny každej položky pekárne uloženej v samostatnej tabuľke vyhľadávania cien a potom na vrátenie tejto ceny do stĺpca Cena/Doz v zozname Denný predaj. Bunka F3 obsahuje pôvodný vzorec s funkciou HLOOKUP, ktorý sa potom skopíruje do stĺpca F:
Použitie funkcie HLOOKUP na vrátenie ceny pekárenskej položky z vyhľadávacej tabuľky.
=HLOOKUP(položka,Cenová_tabuľka,2,FALSE)
V tomto VVYHLEDAT funkcie, názov položky rozsahu, ktorý je daný stĺpci Položka v rozmedzí C3: C62 je definovaná ako lookup_value argumentu a rozsah bunka názov Cena stola, ktorý je daný rozsah buniek I1: M2 je table_array argumentom. Argument row_index_num je 2, pretože chcete, aby Excel vrátil ceny v druhom riadku tabuľky na vyhľadávanie cien, a voliteľný argument range_lookup je FALSE, pretože názov položky v zozname denného predaja sa musí presne zhodovať s názvom položky v tabuľke vyhľadávania cien .
Tým, že funkcia HLOOKUP použije rozsah cenovej tabuľky na zadanie ceny za tucet pre každú položku pekárenského tovaru v zozname denných predajov, bude aktualizácia ktoréhokoľvek z predajov v zozname veľmi jednoduchá. Jediné, čo musíte urobiť, je zmeniť jeho cenu/doz v tomto rozsahu a funkcia HLOOKUP okamžite aktualizuje novú cenu v zozname denných predajov všade, kde sa položka predáva.