Tip
Skúste použiť nové funkcie XLOOKUP a XMATCH , vylepšené verzie funkcií popísaných v tomto článku. Tieto nové funkcie pracujú v ľubovoľnom smere a predvolene vracajú presné zhody, vďaka čomu sa jednoduchšie a pohodlnejšie používajú v porovnaní s ich predchodcami.
Predpokladajme, že máte zoznam čísel kancelárií, a potrebujete vedieť, ktorí zamestnanci sú v jednotlivých kanceláriách. Tabuľkový hárok je obrovský, takže sa vám môže zdať, že ide o náročnú úlohu. S vyhľadávacou funkciou je to vlastne jednoduché.
Funkcie VLOOKUP a HLOOKUP spolu s funkciami INDEX a MATCH patria k najužitočnejším funkciám v Exceli.
Poznámka
Funkcia Sprievodca vyhľadávaním už nie je v Exceli k dispozícii.
Tu je príklad použitia funkcie VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
V tomto príklade je B2 prvým argumentom – prvkom údajov, ktoré funkcia potrebuje, aby fungovala. Pri použití funkcie VLOOKUP predstavuje tento prvý argument hodnotu, ktorú chcete nájsť. Týmto argumentom môže byť odkaz na bunku alebo pevná hodnota, napríklad kováč alebo 21 000. Druhý argument je rozsah buniek, C2-:E7, v ktorých chcete vyhľadať hľadanú hodnotu. Tretí argument je stĺpec v danom rozsahu buniek, ktorý obsahuje hľadanú hodnotu.
Štvrtý argument je voliteľný. Zadajte hodnotu TRUE alebo FALSE. Ak zadáte hodnotu TRUE alebo necháte argument prázdny, funkcia vráti približnú zhodu hodnoty zadanej v prvom argumente. Ak zadáte hodnotu FALSE, funkcia vyhľadá zhodu hodnoty uvedenej v prvom argumente. Inak povedané, vďaka možnosti nechať štvrtý argument prázdny alebo zadať hodnotu TRUE máte k dispozícii väčšiu flexibilitu.
Tento príklad znázorňuje, ako funkcia funguje. Keď zadáte hodnotu do bunky B2 (prvý argument), funkcia VLOOKUP prehľadá bunky v rozsahu C2:E7 (2. argument) a vráti najbližšiu približnú zhodu z tretieho stĺpca v rozsahu, stĺpca E (3. argument).
Štvrtý argument je prázdny a funkcia preto vráti približnú zhodu. Ak by sa tak nestalo, na získanie nejakého výsledku by bolo potrebné zadať jednu z hodnôt v stĺpcoch C alebo D.
Ak ste si oboznámení s funkciou VLOOKUP, rovnako jednoducho sa používa aj funkcia HLOOKUP. Zadávajú sa pri nej rovnaké argumenty, no namiesto v stĺpcoch sa vyhľadáva v riadkoch.
Použitie funkcií INDEX a MATCH namiesto funkcie VLOOKUP
Pri používaní funkcie VLOOKUP existujú určité obmedzenia – funkcia VLOOKUP dokáže vyhľadať hodnoty iba zľava doprava. Znamená to, že stĺpec obsahujúci hľadanú hodnotu by sa mal vždy nachádzať naľavo od stĺpca obsahujúceho vrátenú hodnotu. Ak tabuľkový hárok nie je zostavený týmto spôsobom, nepoužívajte funkciu VLOOKUP. Namiesto toho použite kombináciu funkcií INDEX a MATCH.
V tomto príklade je malý zoznam, v ktorom hľadaná hodnota – Chicago – nie je v stĺpci úplne vľavo. Nemôžeme preto použiť funkciu VLOOKUP. Namiesto toho použijeme funkciu MATCH na nájdenie hodnoty Chicago v rozsahu B1:B11. Nájde sa v riadku 4. Funkcia INDEX potom túto hodnotu použije ako argument vyhľadávania a nájde populáciu pre Chicago vo 4. stĺpci (stĺpec D). Použitý vzorec je zobrazený v bunke A14.
Skúste to
Ak si chcete vyhľadávacie funkcie vyskúšať skôr, než ich použijete s vlastnými údajmi, môžete použiť vzorové údaje uvedené na tomto mieste.
Príklad funkcie VLOOKUP v práci
Skopírujte tieto údaje do prázdneho tabuľkového hárka.
Tip
Pred prilepením údajov do Excelu nastavte šírku stĺpcov A až C na 250 pixlov a kliknite na položku Zalomiť text (karta Domov, skupina Zarovnanie).
| Hustota | Viskozita | Teplota |
|---|---|---|
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | 0 |
| Vzorec | Popis | Výsledok |
| =VLOOKUP(1;A2:C10;2) | Hľadá približnú zhodu k hodnote 1 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 1 v stĺpci A, ktorá je 0,946, a potom vráti hodnotu zo stĺpca B v rovnakom riadku. | 2,17 |
| =VLOOKUP(1;A2:C10;3;TRUE) | Hľadá približnú zhodu k hodnote 1 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 1 v stĺpci A, ktorá je 0,946, a potom vráti hodnotu zo stĺpca C v rovnakom riadku. | 100 |
| =VLOOKUP(0,7;A2:C10;3;FALSE) | Hľadá úplnú zhodu k hodnote 0,7 v stĺpci A. Pretože stĺpec A úplne zhodnú hodnotu neobsahuje, vráti sa chyba. | #N/A |
| =VLOOKUP(0,1;A2:C10;2;TRUE) | Hľadá približnú zhodu k hodnote 0,1 v stĺpci A. Pretože hodnota 0,1 je menšia ako najmenšia hodnota v stĺpci A, vráti sa chyba. | #N/A |
| =VLOOKUP(2;A2:C10;2;TRUE) | Hľadá približnú zhodu k hodnote 2 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 2 v stĺpci A, ktorá je 1,29, a potom vráti hodnotu zo stĺpca B v rovnakom riadku. | 1,71 |
Príklad HLOOKUP
Skopírujte všetky bunky v tejto tabuľke a vložte ich do bunky A1 prázdneho hárka v Exceli.
Tip
Pred prilepením údajov do Excelu nastavte šírku stĺpcov A až C na 250 pixlov a kliknite na položku Zalomiť text (karta Domov, skupina Zarovnanie).
| Nápravy | Ložiská | Skrutky |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Vzorec | Popis | Výsledok |
| =HLOOKUP("Nápravy"; A1:C4; 2; TRUE) | Vyhľadá výraz „Nápravy" v prvom riadku a vráti hodnotu z druhého riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci A). | 4 |
| =HLOOKUP("Ložiská"; A1:C4; 3; FALSE) | Vyhľadá výraz „Ložiská" v prvom riadku a vráti hodnotu z tretieho riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci B). | 7 |
| =HLOOKUP("B"; A1:C4; 3; TRUE) | Vyhľadá hodnotu B v riadku 1 a vráti hodnotu z riadka 3 toho istého stĺpca. Vzhľadom na to, že presná zhoda sa nenašla, použije sa najväčšia hodnota v prvom riadku, ktorá je súčasne menšia ako hodnota B: Nápravy v stĺpci A | 5 |
| =HLOOKUP("Svorníky"; A1:C4; 4) | Vyhľadá výraz „Svorníky" v prvom riadku a vráti hodnotu zo štvrtého riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci C). | 11 |
| =HLOOKUP(3; {1,2,3;"a","b","c";"d","e","f"}; 2; TRUE) | Vyhľadá číslo 3 v trojriadkovej konštante poľa a vráti hodnotu z druhého riadka toho istého (v tomto prípade tretieho) stĺpca. V konštante poľa sa nachádzajú tri riadky s hodnotami, jednotlivé riadky sú oddelené bodkočiarkou (;). Keďže hodnota c sa nachádza v druhom riadku a v tom istom stĺpci ako číslo 3, vráti sa hodnota c. | c |
Príklady funkcií INDEX a MATCH
V tomto poslednom príklade sa spoločne používajú funkcie INDEX a MATCH na vrátenie čísla najstaršej faktúry a jej zodpovedajúceho dátumu pre každé z piatich miest. Keďže dátum sa vráti ako číslo, pomocou funkcie TEXT sa toto číslo naformátuje ako dátum. Funkcia INDEX používa výsledok funkcie MATCH ako svoj argument. Kombinácia funkcií INDEX a MATCH sa v každom vzorci použije dvakrát, najskôr na vrátenie čísla faktúry, potom na vrátenie dátumu.
Skopírujte všetky bunky v tejto tabuľke a vložte ich do bunky A1 prázdneho hárka v Exceli.
Tip
Pred prilepením údajov do Excelu nastavte šírku stĺpcov A až C na 250 pixlov a kliknite na položku Zalomiť text (karta Domov, skupina Zarovnanie).
| Faktúra | Mesto | Dátum vystavenia faktúry | Najstaršia faktúra podľa mesta s uvedením dátumu |
|---|---|---|---|
| 3115 | Brezno | 07.04.12 | ="Brezno = "&INDEX($A$2:$C$33;MATCH("Brezno";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Brezno";$B$2:$B$33;0);3);"d/m/rr") |
| 3137 | Brezno | 09.04.12 | ="Bernolákovo = "&INDEX($A$2:$C$33;MATCH("Bernolákovo";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Bernolákovo";$B$2:$B$33;0);3);"d/m/rr") |
| 3154 | Brezno | 11.04.12 | ="Dudince = "&INDEX($A$2:$C$33;MATCH("Dudince";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Dudince";$B$2:$B$33;0);3);"d/m/rr") |
| 3191 | Brezno | 21.04.12 | ="Nové Zámky = "&INDEX($A$2:$C$33;MATCH("Nové Zámky";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Nové Zámky";$B$2:$B$33;0);3);"d/m/rr") |
| 3293 | Brezno | 25.04.12 | ="Trnava = "&INDEX($A$2:$C$33;MATCH("Trnava";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Trnava";$B$2:$B$33;0);3);"d/m/rr") |
| 3331 | Brezno | 27.04.12 | |
| 3350 | Brezno | 28.04.12 | |
| 3390 | Brezno | 01.05.12 | |
| 3441 | Brezno | 02.05.12 | |
| 3517 | Brezno | 08.05.12 | |
| 3124 | Bernolákovo | 09.04.12 | |
| 3155 | Bernolákovo | 11.04.12 | |
| 3177 | Bernolákovo | 19.04.12 | |
| 3357 | Bernolákovo | 28.04.12 | |
| 3492 | Bernolákovo | 06.05.12 | |
| 3316 | Dudince | 25.04.12 | |
| 3346 | Dudince | 28.04.12 | |
| 3372 | Dudince | 01.05.12 | |
| 3414 | Dudince | 01.05.12 | |
| 3451 | Dudince | 02.05.12 | |
| 3467 | Dudince | 02.05.12 | |
| 3474 | Dudince | 04.05.12 | |
| 3490 | Dudince | 05.05.12 | |
| 3503 | Dudince | 08.05.12 | |
| 3151 | Nové Zámky | 09.04.12 | |
| 3438 | Nové Zámky | 02.05.12 | |
| 3471 | Nové Zámky | 04.05.12 | |
| 3160 | Trnava | 18.04.12 | |
| 3328 | Trnava | 26.04.12 | |
| 3368 | Trnava | 29.04.12 | |
| 3420 | Trnava | 01.05.12 | |
| 3501 | Trnava | 06.05.12 |
Pozrite tiež
Stručná referenčná karta: Rýchly prehľad funkcie VLOOKUP
Vyhľadávacie a referenčné funkcie (odkaz)