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

Predstavte si situáciu, že máte zoznam čísel kancelárií, a potrebujete vedieť, ktorí zamestnanci sú v jednotlivých kanceláriách. Tabuľka je však obrovská, čo teda môžete urobiť? Použite vyhľadávaciu funkciu. Jedny z najužitočnejších sú funkcie VLOOKUP a HLOOKUP a takisto aj funkcie INDEX a MATCH.

Poznámka :  Ak sa pokúšate nájsť Sprievodcu vyhľadávaním, táto funkcia už nie je súčasťou Excelu.

Teraz si pripomeňme, ako sa používa funkcia VLOOKUP.

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

Prvý argument – údaj, ktorý funkcia potrebuje na fungovanie – je hodnota, ktorú chcete nájsť. Môže to byť odkaz na bunku alebo konkrétna hodnota, napríklad kováč alebo 21 000. Druhý argument je rozsah buniek, ktorý podľa vás obsahuje hľadanú hodnotu. V tomto príklade je to rozsah C2 – C7. Tretí argument je stĺpec v danom rozsahu buniek, ktorý obsahuje požadovanú hodnotu.

Štvrtý argument je voliteľný. Môžete zadať 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 C2 – E7 (druhý argument) a vráti najbližšiu približnú zhodu z tretieho stĺpca v rozsahu, stĺpca E (tretí argument).

Typické použitie funkcie VLOOKUP

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

Po osvojení funkcie VLOOKUP už nebude ťažké používať ani funkciu HLOOKUP. Zadávajú sa pri nej rovnaké argumenty, no namiesto v stĺpcoch vyhľadáva hodnoty v riadkoch.

Skúste to

Ak si chcete prácu s vyhľadávacími funkciami vyskúšať skôr, než ich použijete s vlastnými údajmi, môžete použiť vzorové údaje uvedené na tomto mieste. Niektorí ľudia radi používajú funkcie VLOOKUP a HLOOKUP, iní zas uprednostňujú spoločné používanie funkcií INDEX a MATCH. Vyskúšajte si každú z týchto metód a zistite, ktorá vám najviac vyhovuje.

Funkcia VLOOKUP pri práci

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

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.

=VLOOKUP(1;A2:C10;2)

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

=VLOOKUP(1;A2:C10;3;TRUE)

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

=VLOOKUP(0,7;A2:C10;3;FALSE)

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

=VLOOKUP(0,1;A2:C10;2;TRUE)

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

=VLOOKUP(2;A2:C10;2;TRUE)

Funkcia HLOOKUP pri práci

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 riadku 1 a vráti hodnotu z riadka 2, ktorá sa nachádza v tom istom stĺpci (stĺpci A).

=HLOOKUP("Nápravy";A1:C4;2;TRUE)

'=HLOOKUP("Ložiská"; A1:C4; 3; FALSE)

Vyhľadá výraz „Ložiská" v riadku 1 a vráti hodnotu z riadka 3, ktorá sa nachádza v tom istom stĺpci (stĺpci B).

=HLOOKUP("Ložiská";A1:C4;3;FALSE)

'=HLOOKUP("O"; A1:C4; 3; TRUE)

Vyhľadá hodnotu O v riadku 1 a vráti hodnotu z riadka 3 toho istého stĺpca. Vzhľadom na to, že presná zhoda hodnoty O sa nenašla, použije sa najväčšia hodnota v riadku 1, ktorá je súčasne menšia ako hodnota O: hodnota Nápravy v stĺpci A

=HLOOKUP("O";A1:C4;3;TRUE)

'=HLOOKUP("Skrutky"; A1:C4; 4)

Vyhľadá výraz „Skrutky" v riadku 1 a vráti hodnotu z riadka 4, ktorá sa nachádza v tom istom stĺpci (stĺpci C).

=HLOOKUP("Skrutky";A1:C4;4)

'=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 riadka 2 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 riadku 2 a v tom istom stĺpci ako číslo 3, vráti sa hodnota c.

=HLOOKUP(3;{1;2;3;"a";"b";"c";"d";"e";"f"};2;TRUE)

Funkcie INDEX a MATCH v akcii

V tomto 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

Ďalšie informácie o vyhľadávacích funkciách

Na začiatok stránky

Rozšírte svoje zručnosti
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.

×