Oprava chyby #NEDOSTUPNÝ vo funkcii VLOOKUP

V tejto téme sú popísané najbežnejšie možné dôvody zlyhania funkcie VLOOKUP.

Tip : Pozrite si tému Stručná referenčná karta: Tipy na riešenie problémov s funkciou VLOOKUP, ktorá obsahuje praktický PDF súbor (Portable Document Format) popisujúci bežné dôvody chýb #NEDOSTUPNÝ pri použití funkcie VLOOKUP. Tento PDF súbor môžete zdieľať s ostatnými používateľmi alebo ho vytlačiť pre vlastné potreby.

Problém: Hľadaná hodnota nie je v prvom stĺpci v argumente pole_tabuľky

Jedným z najväčších obmedzení funkcie VLOOKUP je, že môže hľadať len hodnoty v ľavom krajnom stĺpci poľa tabuľky. Ak teda hľadaná hodnota nie je v prvom stĺpci poľa, zobrazí sa chyba #NEDOSTUPNÝ.

V nasledujúcej tabuľke chceme načítať množstvo predaného kelu.

Chyba #NEDOSTUPNÝ vo funkcii VLOOKUP: Hľadaná hodnota nie je v prvom stĺpci poľa tabuľky

Chyba je spôsobená tým, že hľadaná hodnota kel je v druhom stĺpci (Produkt) v argumente pole_tabuľky A2:C10, a preto ju Excel hľadá v stĺpci A, nie v stĺpci B.

Riešenie: Tento problém môžete skúsiť vyriešiť úpravou funkcie VLOOKUP tak, aby odkazovala na správny stĺpec. Ak to nie je možné, skúste presunúť stĺpce. To môže byť veľmi nepraktické, ak máte veľké alebo zložité tabuľky, ktorých hodnoty buniek sú výsledkami iných výpočtov, alebo je možné, že stĺpce jednoducho nemôžete presunúť z iných logických dôvodov. Riešením je použiť kombináciu funkcií INDEX a MATCH, ktoré dokážu vyhľadať hodnotu v stĺpci bez ohľadu na jeho pozíciu vo vyhľadávacej tabuľke.

Použitie funkcií INDEX a MATCH namiesto funkcie VLOOKUP

Keď funkcia VLOOKUP nespĺňa vaše potreby, môžete použiť funkcie INDEX a MATCH. Najväčšou výhodou funkcií INDEX a MATCH je, že môžete vyhľadať hodnoty v stĺpci v ľubovoľnom umiestnení vo vyhľadávacej tabuľke. Funkcia INDEX vráti hodnotu zo zadanej tabuľky alebo rozsahu na základe jej pozície a funkcia MATCH vráti relatívnu pozíciu hodnoty v tabuľke alebo rozsahu. Súčasným použitím funkcií INDEX a MATCH vo vzorci môžete vyhľadať hodnoty v tabuľke alebo poli tak, že zadáte relatívnu pozíciu hodnoty v tabuľke alebo poli.

Použitie funkcií INDEX a MATCH namiesto funkcie VLOOKUP má viacero výhod:

  • Na rozdiel od funkcie VLOOKUP, pri ktorej sa vrátená hodnota musí nachádzať v zadanom rozsahu, pri použití funkcií INDEX a MATCH sa vrátená hodnota nemusí nachádzať v rovnakom stĺpci ako vyhľadávací stĺpec. Prečo na tom záleží? Pri použití funkcie VLOOKUP je potrebné poznať číslo stĺpca, ktorý obsahuje vrátenú hodnotu. Hoci to neznie ako dôležité, môže to byť komplikované vtedy, keď máte veľkú tabuľku a musíte spočítať počet stĺpcov. Navyše ak do tabuľky pridáte stĺpec alebo ho z nej odstránite, musíte počet stĺpcov prepočítať a aktualizovať argument číslo_indexu_stĺpca. Pri použití funkcií INDEX a MATCH nie je potrebné žiadne počítanie, pretože vyhľadávací stĺpec sa líši od stĺpca, ktorý obsahuje vrátenú hodnotu.

  • Pri použití funkcií INDEX a MATCH môžete zadať riadok alebo stĺpec v poli, alebo zadať aj oboje. To znamená, že môžete vyhľadávať hodnoty zvislo aj vodorovne.

  • Pomocou funkcií INDEX a MATCH možno hľadať hodnoty v ľubovoľnom stĺpci. Na rozdiel od funkcie VLOOKUP, s ktorou môžete vyhľadať len hodnotu v prvom stĺpci v tabuľke, funkcie INDEX a MATCH budú fungovať bez ohľadu na to, či sa hľadaná hodnota nachádza v prvom stĺpci, poslednom stĺpci alebo hocikde medzi nimi.

  • Použitie funkcií INDEX a MATCH ponúka flexibilitu v tom, že vytvára dynamický odkaz na stĺpec obsahujúci vrátenú hodnotu. To znamená, že funkcie INDEX a MATCH budú fungovať aj potom, ako do tabuľky pridáte stĺpce. Naopak funkcia VLOOKUP bude po pridaní stĺpca nefunkčná, pretože vytvára statický odkaz na tabuľku.

  • Funkcie INDEX a MATCH ponúkajú väčšiu flexibilitu v zhodách. Funkcie INDEX a MATCH dokážu nájsť presnú zhodu, ako aj hodnotu menšiu alebo väčšiu ako hľadaná hodnota. Funkcia VLOOKUP vyhľadá len najbližšiu zodpovedajúcu hodnotu (predvolene) alebo presnú hodnotu. Funkcia VLOOKUP tiež predvolene predpokladá, že prvý stĺpec v tabuľke je zoradený v abecednom poradí. V prípade, že tabuľka nie je nastavená takto, funkcia VLOOKUP vráti prvú najbližšiu zodpovedajúcu hodnotu v tabuľke, a tá možno nebude predstavovať údaj, ktorý hľadáte.

Syntax

Pri zostavovaní syntaxe funkcií INDEX a MATCH je potrebné použiť argument poľa alebo odkazu z funkcie INDEX a vnoriť do neho syntax funkcie MATCH. Vyzerá to asi takto:

=INDEX(pole alebo rozsah; MATCH(vyhľadávaná_hodnota;pole_vyhľadávania;[typ_zhody])

Nahraďme funkciu VLOOKUP vo vyššie uvedenom príklade funkciami INDEX a MATCH. Syntax bude vyzerať takto:

=INDEX(C2:C10;MATCH(B13;B2:B10;0))

Ak to zjednodušíme, táto syntax znamená:

=INDEX(chcem vrátenú hodnotu z rozsahu C2:C10, ktorá predstavuje ZHODU(hodnota Kel, ktorá sa nachádza niekde v poli B2:B10, pričom vrátená hodnota je prvá hodnota zodpovedajúca hodnote Kel))

Namiesto funkcie VLOOKUP je možné použiť funkcie INDEX a MATCH

Vzorec vyhľadá prvú hodnotu v rozsahu C2:C10, ktorá zodpovedá hodnote Kel (v bunke B7), a vráti hodnotu v bunke C7 (100), ktorá je prvou hodnotou zodpovedajúcou hodnote Kel.

Problém: Presná zhoda sa nenašla

Keď má argument vyhľadávanie_rozsahu hodnotu FALSE a funkcia VLOOKUP nemôže v údajoch nájsť presnú zhodu, vráti chybu #NEDOSTUPNÝ.

Riešenie: Ak ste si istí, že tabuľka relevantné údaje obsahuje, no funkcia VLOOKUP ich nevie zachytiť, skontrolujte, či odkazované bunky neobsahujú skryté medzery alebo netlačiteľné znaky. Skontrolujte tiež, či bunky patria pod správny typ údajov. Napríklad bunky obsahujúce čísla majú byť formátované ako číslo, nie ako text.

Vyčistite údaje v bunkách pomocou funkcie CLEAN alebo TRIM.

Problém: Hľadaná hodnota je menšia ako najmenšia hodnota v poli

Ak je argument vyhľadávanie_rozsahu nastavený na hodnotu TRUE a hľadaná hodnota je menšia ako najmenšia hodnota v poli, zobrazí sa chyba #NEDOSTUPNÝ. Hodnota TRUE hľadá približnú zhodu v poli a vráti najbližšiu hodnotu menšiu ako hľadaná hodnota.

V nasledujúcom príklade je hľadaná hodnota 100, no v rozsahu B2:C10 nie sú žiadne hodnoty menšie ako 100, a to spôsobuje chybu.

Chyba #NEDOSTUPNÝ vo funkcii VLOOKUP, ak je hľadaná hodnota menšia ako najmenšia hodnota v poli

Riešenie:

  • Opravte hľadanú hodnotu podľa potreby.

  • Ak hľadanú hodnotu nemožno zmeniť a potrebujete väčšiu flexibilitu v zodpovedajúcich hodnotách, zvážte použitie funkcií INDEX a MATCH namiesto funkcie VLOOKUP. Pomocou funkcií INDEX a MATCH môžete hľadať hodnoty, ktoré sú väčšie ako, menšie ako alebo rovnaké ako hľadaná hodnota. Ďalšie informácie o použití funkcií INDEX a MATCH namiesto funkcie VLOOKUP nájdete v predchádzajúcej časti tejto témy.

Problém: Vyhľadávací stĺpec nie je zoradený vo vzostupnom poradí

Ak je argument vyhľadávanie_rozsahu nastavený na hodnotu TRUE a niektorý z vyhľadávacích stĺpcov nie je zoradený vo vzostupnom poradí (A – Z), zobrazí sa chyba #NEDOSTUPNÝ.

Riešenie:

  • Zmeňte funkciu VLOOKUP tak, aby hľadala presnú zhodu. Ak tak chcete urobiť, nastavte argument vyhľadávanie_rozsahu na hodnotu FALSE. Pri nastavení na hodnotu FALSE nie je potrebné žiadne zoradenie.

  • Na vyhľadanie hodnoty v nezoradenej tabuľke použite funkcie INDEX a MATCH.

Problém: Hodnota je veľké číslo s pohyblivou desatinnou čiarkou

Ak máte v bunkách časové údaje alebo čísla s veľkým počtom desatinných miest, Excel vráti chybovú hodnotu #NEDOSTUPNÝ pre pohyblivú desatinnú čiarku. Čísla s pohyblivou desatinnou čiarkou sú tie, ktoré nasledujú po desatinnej čiarke. (Všimnite si, že Excel ukladá časové údaje ako čísla s pohyblivou desatinnou čiarkou.) Excel nedokáže uložiť veľké čísla s pohyblivou desatinnou čiarkou, preto je na správne fungovanie funkcie potrebné zaokrúhliť čísla s pohyblivou desatinnou čiarkou na 5 desatinných miest.

Riešenie: Skráťte čísla ich zaokrúhlením na päť desatinných miest s použitím funkcie ROUND.

Máte otázku ku konkrétnej funkcii?

Uverejnite otázku vo fóre komunity používateľov Excelu

Pomôžte nám pri zlepšovaní Excelu

Máte nejaký nápad, ako by sme mohli zlepšiť nasledujúcu verziu Excelu? Ak áno, pozrite sa na témy na lokalite Excel User Voice.

Pozrite tiež

Odstránenie chyby #NEDOSTUPNÝ

VLOOKUP: už žiadne ďalšie chyby #NEDOSTUPNÝ

Funkcie HLOOKUP, VLOOKUP, LOOKUP vracajú v Exceli nesprávne hodnoty

Výsledky výpočtov s číslami s pohyblivou desatinnou čiarkou môžu byť v Exceli nepresné

Stručná referenčná karta: Rýchly prehľad funkcie VLOOKUP

VLOOKUP (funkcia)

Prehľad vzorcov v Exceli

Zabránenie vzniku nefunkčných vzorcov

Zisťovanie chýb vo vzorcoch pomocou kontroly chýb

Zoznam funkcií Excelu (podľa abecedy)

Zoznam funkcií Excelu (podľa kategórie)

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.

×