SVYHLEDAT (VLOOKUP) - číslo vs text Excel

Úvodem

Často se setkávám s problémem při hledání hodnot s využitím funkce SVYHLEDAT (VLOOKUP). Ač podle návodu v prohledávané tabulce máte jedinečné hodnoty v prvním sloupci, ve správné tabulce, hledáte přesnou shodu, přes argument NEPRAVDA (FALSE). Přesto funkce hledanou hodnotu nenajde. Vyhledáte-li ručně (využitím Najít - Ctrl + F) a hodnotu v tabulce vidíte.

Co může být příčinou, jak problém najít a jak vyhledávací funkci upravit (aniž musíte upravovat zdrojová data) je popsáno v tomto článku. Z důvodu přehlednosti je článek rozdělen na kapitoly:

• Trocha teorie
• Zdrojová tabulka
• Jak zjistit o co jde - číslo nebo text
• SVYHLEDAT jak zapsat
• Další problémy - počet znaků
• Související články
• Ke stažení

Trocha teorie

Příčinou může být (kromě duplicitních názvu, opomenutí přesné shody, spravné označení tabulky ...) nekonzistence mezi textem a číslem (myslíte si, že v prvním sloupci prohledávané tabulky je číslo, ale opak je pravdou, ve skutečnosti může jít o text, případně opačně). Nebo-li například hodnota 1 (kdy vy vydíte číslo, jde přece o "jedničku"), Excel ve skutečnosti buď nemusí vidět číslo, ale vidí text. Což je pro vyhledávací funkci rozdíl (text není číslo). Jak na to? Jak opravit:

• Opravit ve zdrojových datech - zajistit shodný formát (většinou zvládne správce databáze a máte vyřešeno)
• Opravit v Excel - využitím textových funkcí, Power Query...
• Napsat lépe funkci SVYHLEDAT - využít HODNOTA (VALUE), případně HODNOTA.NA.TEXT (TEXT)

Další komplikace

U vyhledávací funkce SVYHLEDAT se můžete setkat s dalšími komplikacemi (což většinou odhalíte a vyřešíte sami):

• duplicitní hodnoty - ve zdrojové (prohledávané tabulce) máte duplicitní hodnoty
• neseřazeno - pokud používáte funkci v přibližné shodě nemáte zdrojovou - prohledávanou tabulku správně seřazenou
• Přesná shoda - opomenutý čtvrtý argument NEPRAVDA (FALSE) pro hledaní na přesnou shodu
• "Nauzamčena" tabulka - Zapomenete přidat absolutní odkazy neboli $, tím pádem dochází k posouvaní oblastí a opět funkce SVYLHEDAT nemusí fungovat správně.
• Více znaků - mezery, které nevidíté, ale třeba zdrojová data, nebo hledaný text je obsahuji speciálmní znaky (mezery, tvrdá mezera). Vy mezeru vidět nemusíte, ale Excel ji vidí. Pes vs Pes_ je rozdíl (druhé slovo obsahuje mezeru). Tip využít funkci DÉLKA (LEN) na zjištění počtu znaku.
• Text vs číslo - tohle si probereme v tomto článku

Zdrojová tabulka

Pro ukázku potřebujeme data. Připravíme si data, obsahují v prvním sloupci (prohledávaném) čísla i text a ve druhém informaci ať vím co je v kterém řádku a využijeme při hledání.

• Číslo jedna, je zapsáno nejprve jako číslo a podruhé jako text.
• Číslo dvě je zapsáno jako text.
• Číslo tři je zapsáno jako číslo.

Co budeme hledat?

Abych ukázal jak se funkce SVYHLEDAT chová. Předvedu na hledání hodnot jedna až tři. Kdy každá hodnota je zapsána jednou jako číslo a jednou jako text (tj. budeme hledat 6 hodnot, teda sedm, jednou jsou číslu přiřazeny desetinná místa, ať je pochopení jak funkce SVYHLEDAT pracuje dokonalé).

Jak zjistit o co jde

Jak co nejefektivněji zjistit, zda v buňce je text nebo číslo (že si něco myslíte je jedná věc, ale vy to musíte vědět přesně). Několik možností zjištění, co je v buňce text/číslo:

• Přidat odebrat desetinná místa - nejrychlejší pokud se jedná o jednu buňku ve které vidíte číslo a chcete si být jisti na 100%, stačí na pásu karet Domů přidat nebo ubrat desetinná místa.
• Využít funkci JE.ČÍSLO (ISNUMBER) - funkce vrátí PRAVDA (TRUE) pokud v buňce je číslo, jinak vrátí NEPRAVDA (FALSE) - pak víte, že v buňce je text
• Využít funkci JE.TEXT (ISTEXT)

SVYHLEDAT jak zapsat

Teď když víme, že ve zdrdojové tabulce se některé hodnoty vyskytují jako číslo a některé jako text. A já hledám hodnoty ať už jako text, nebo číslo, ne vždy dojde k správnému propojení, ale pokud funkci SVYHLEDAT upravíte, doplníte o funkce HODNOTA (VALUE), případně HODNOTA.NA.TEXT (TEXT), můžete dostat úplně jiné výsledky. Případně pokud doplníte o funkci IFERROR ;)

Pokud jen vyhledáte přes SVYHLEDAT, funkce některé hodnoty nenajde. Nebo-li pokud hledáte číslo a v zdrojové tabulce je text, tak pochopitelně nedojde k nalezení.

=SVYHLEDAT(B5;$B$22:$C$25;2;NEPRAVDA)

Využitím funkci HODNOTA (VALUE), případně HODNOTA.NA.TEXT (TEXT), můžete dohledat patřičné hodnoty (většinou v praxi stačí), pokud už jde o zmatek v datech.

=SVYHLEDAT(HODNOTA.NA.TEXT(B5;"@");$B$22:$C$25;2;NEPRAVDA) =SVYHLEDAT(HODNOTA(B5);$B$22:$C$25;2;NEPRAVDA)

Pokud ale jsou různá data ať už na straně zdrojové tabulky tak i na straně vyhledávání, můžete využít kombinace, kdy se pokusíte nejprve najít text a posléze číslo (případně opačně). Navíc můžete ještě obalit funkci IFERROR a místo #NENÍ_K_DISPOZICI zobrazit, třeba nenalezeno.

=IFERROR(SVYHLEDAT(HODNOTA(B5);$B$22:$C$25;2;NEPRAVDA);SVYHLEDAT(HODNOTA.NA.TEXT(B5;"@");$B$22:$C$25;2;NEPRAVDA)) =IFERROR(SVYHLEDAT(HODNOTA.NA.TEXT(B5;"@");$B$22:$C$25;2;NEPRAVDA);SVYHLEDAT(HODNOTA(B5);$B$22:$C$25;2;NEPRAVDA))

Poznámka: Vím, nejjednodušší je vyřešit ve zdrojové tabulce (jen někdy ta možnost není a potřebujete rychle propojit tak i některou z výše uvedených možností můžete použít).

Další problémy

Pro řešení dalších problémů (více znaků v datech), můžete využít funkci DÉLKA (LEN).

Související články

Další související články:

• SVYHLEDAT (VLOOKUP) - od A do Z - tipy triky
• JE.ČÍSLO (ISNUMBER
• Je.TEXT (ISTEXT)
• Jak na Power Query

Ke stažení

Soubor ke stažení zdarma je v přípravě. Patreon a váš příspěvek zajistí rychlejší publikaci ;).

Závěrem

Setkali jste se i s jiným problémem u funkce SVYHLEDAT? Můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:00

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.