Táto téma popisuje najbežnejšie dôvody chyby funkcie VLOOKUP a poskytuje návrhy na použitie funkcií INDEX a MATCH .
Tip: Pozrite sa tiež na stručnú referenčnú kartu: Funkcia SVYHĽADAŤ – tipy na odstraňovanie problémov, ktoré predstavujú bežné dôvody chýb #NIE JE_K_DISPOZÍCII v praktickom súbore PDF. 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 obmedzení funkcie SVYHĽADAŤ je, že môže hľadať iba hodnoty v stĺpci tabuľky, ktorý je najviac vľavo. Ak 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 #NIEJE_K_DISPOZÍCII, pretože v druhom stĺpci (Plodiny) argumentu table_array A2:C10 sa zobrazí vyhľadávacia hodnota Kel. V tomto prípade 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 tiež 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. Pozrite sa na ďalšiu časť.
Zvážte použitie funkcie INDEX/POZVYHĽADAŤ.
Funkcie INDEX a POZVYHĽADAŤ sú vhodnými možnosťami pre mnoho prípadov, keď funkcia SVYHĽADAŤ nevyhovuje vašim potrebám. Hlavnou 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 oblasti podľa jej pozície. Funkcia POZVYHĽADAŤ vráti relatívnu pozíciu hodnoty v tabuľke alebo oblasti. 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:
-
Pri funkciách INDEX a POZVYHĽADAŤ nemusí byť vrátená hodnota v rovnakom stĺpci ako je prehľadávaný stĺpec. To sa líši od funkcie SVYHĽADAŤ, v ktorej musí byť vrátená hodnota v zadanom rozsahu. 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 môžete do tabuľky pridať stĺpce bez porušenia funkcií INDEX a POZVYHĽADAŤ. 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. Tento formulár má nasledujúcu podobu:
=INDEX(pole alebo rozsah; MATCH(vyhľadávaná_hodnota;pole_vyhľadávania;[typ_zhody])
Pomocou funkcie INDEX/POZVYHĽADAŤ nahradíme funkciu SVYHĽADAŤ z vyššie uvedeného príkladu. Syntax bude vyzerať takto:
=INDEX(C2:C10;MATCH(B13;B2:B10;0))
Ak to zjednodušíme, táto syntax znamená:
=INDEX(crátiť 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))
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.
Zvážte aj použitie funkcie VYČISTIŤ alebo PROČISTIŤ na vyčistenie údajov v bunkách.
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.
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 – pozrite si sekciu článku vyššie. 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. Pre hodnotu NEPRAVDA nie je nutné žiadne radenie.
-
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. (Excel ukladá časové údaje ako čísla s pohyblivou desatinnou čiarkou.) Excel nedokáže uložiť veľmi 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.
Potrebujete ďalšiu pomoc?
Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.
