Vyhľadávanie hodnôt pomocou funkcií VLOOKUP, INDEX a MATCH

Tip: Skúste použiť nové funkcie XLOOKUP a XMATCH , vylepšené verzie funkcií popísaných v tomto článku. Tieto nové funkcie fungujú v ľubovoľnom smere a na základe predvoleného nastavenia vrátia presné výsledky, vďaka čomu sú jednoduchšie a pohodlnejšie na používanie ako ich predchodcovia.

Predpokladajme, že máte zoznam čísiel umiestnení balíka Office a potrebujete vedieť, ktorí zamestnanci sú v jednotlivých Office. Tabuľkový hárok je obrovský, takže si možno myslíte, že je náročná úloha. Je to vlastne celkom jednoduché s funkciou lookup.

Funkcie VLOOKUP a HLOOKUP spolu s indexom a zhodoupredstavujú niektoré z najužitočnejších funkcií v Exceli.

Poznámka: Funkcia Sprievodca vyhľadávaním už nie je v Exceli k dispozícii.

Tu je príklad používania funkcie VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

V tomto príklade je bunka B2 prvým argumentom– prvkom údajov, ktorý funkcia potrebuje na prácu. V prípade funkcie VLOOKUP je tento prvý argument hodnotou, ktorú chcete vyhľadať. Tento argument môže byť odkaz na bunku alebo stála hodnota, napríklad "Smith" alebo 21 000. Druhým argumentom je rozsah buniek, C2-: E7, v ktorom sa má vyhľadať hodnota, ktorú chcete vyhľadať. Tretí argument je stĺpec v rozsahu buniek, ktorý obsahuje požadovanú 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. Inými slovami, ponechanie štvrtého argumentu prázdne – alebo zadanie hodnoty TRUE – vám poskytuje väčšiu flexibilitu.

Tento príklad znázorňuje, ako funkcia funguje. Keď zadáte hodnotu do bunky B2 (prvý argument), funkcia VLOOKUP vyhľadá bunky v rozsahu C2: E7 (2. argument) a vráti najbližšiu približnú zhodu z tretieho stĺpca v rozsahu, stĺpci E (3. argument).

Typické použitie funkcie VLOOKUP

Štvrtý argument je prázdny, takže funkcia 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.

Keď budete spokojní s funkciou VLOOKUP, funkcia HLOOKUP sa rovnako jednoducho používa. Zadáte rovnaké argumenty, ale namiesto stĺpcov sa vyhľadáva v riadkoch.

Používanie funkcií INDEX a MATCH namiesto funkcie VLOOKUP

Pri používaní funkcie VLOOKUP sa používajú určité obmedzenia – funkcia VLOOKUP môže vyhľadať len hodnotu zľava doprava. To znamená, že stĺpec obsahujúci hodnotu, ktorú hľadáte, by sa mal vždy nachádzať naľavo od stĺpca obsahujúceho vrátenú hodnotu. Ak tabuľkový hárok nie je vytvorený týmto spôsobom, nepoužívajte funkciu VLOOKUP. Namiesto toho použite kombináciu funkcií INDEX a MATCH.

V tomto príklade sa zobrazuje malý zoznam, v ktorom je hodnota, ktorú chcete hľadať, v Chicagu, nie je v stĺpci úplne vľavo. Funkcia VLOOKUP sa preto nedá použiť. Namiesto toho použijeme funkciu MATCH na nájdenie Chicaga v rozsahu B1: B11. Nachádza sa v riadku 4. Potom INDEX použije túto hodnotu ako argument vyhľadávania a nájde obyvateľstvo pre Chicago v štvrtom stĺpci (stĺpec D). Použitý vzorec sa zobrazuje v bunke A14.

Použitie funkcií INDEX a MATCH na vyhľadávanie hodnoty

Ďalšie príklady použitia funkcií INDEX a MATCH namiesto funkcie VLOOKUP nájdete v článku https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill jelen, Microsoft MVP.

Skúste to

Ak chcete experimentovať s vyhľadávacími funkciami skôr, než ich vyskúšate s vlastnými údajmi, tu je niekoľko vzorových údajov.

Príklad funkcie VLOOKUP v práci

Skopírujte nasledujúce ú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.

#NEDOSTUPNÝ

=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.

#NEDOSTUPNÝ

=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 INDEXov a ZHÔD

Tento posledný príklad využíva funkcie INDEX a MATCH spolu na vrátenie najskoršieho čísla faktúry a jeho 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

Dallas

25.04.12

3346

Dallas

28.04.12

3372

Dallas

01.05.12

3414

Dallas

01.05.12

3451

Dudince

02.05.12

3467

Dallas

02.05.12

3474

Dallas

04.05.12

3490

Dallas

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

Tichá Lomnica

18.04.12

3328

Tichá Lomnica

26.04.12

3368

Tichá Lomnica

29.04.12

3420

Tichá Lomnica

01.05.12

3501

Trnava

06.05.12

Pozrite tiež

Stručná referenčná karta:

vyhľadávacie a referenčné funkcie funkcie VLOOKUP (odkaz)

používajú Argument Table_array vo funkcii VLOOKUP

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×