Vyhledávací funkce SVYHLEDAT a XLOOKUP v praxi

Funkce SVYHLEDAT najde zadanou hodnotu v prvním sloupci tabulky a vrátí údaj ze stejného řádku v jiném sloupci – typicky doplní k číslu produktu jeho název nebo cenu z ceníku. Čtvrtý argument nastavujte na 0, tedy NEPRAVDA, jinak funkce hledá jen přibližně a může vrátit údaj z cizího řádku. V novějších verzích ji nahrazuje XLOOKUP, který je pružnější a přesnou shodu používá automaticky.

Čtyři argumenty funkce SVYHLEDAT

Zápis v českém Excelu je SVYHLEDAT(hledat; tabulka; sloupec; [typ]). První argument je hodnota, kterou hledáte, třeba kód produktu v buňce A2. Druhý je oblast, ve které se hledá, a její první sloupec musí obsahovat právě ty kódy. Třetí je pořadové číslo sloupce v této oblasti, ze kterého se má vrátit výsledek. Čtvrtý, nepovinný, určuje typ shody.

Modelový příklad: ve sloupci A jsou kódy objednaných produktů, první z nich v buňce A2, a ceník leží opodál ve sloupcích F až H, kde je kód, název a cena. Vzorec =SVYHLEDAT(A2; $F$2:$H$500; 3; 0) vrátí cenu. Znaky dolaru oblast ukotví, aby se při kopírování vzorce dolů neposouvala – zapomenutá kotva je příčina toho, že vzorec v horních řádcích funguje a níž začne vracet chyby.

Anglický název funkce je VLOOKUP a setkáte se s ním například v Tabulkách Google, pokud v nich nemáte zapnuté lokalizované názvy funkcí.

Proč skoro vždy zadat přesnou shodu

Když čtvrtý argument vynecháte, funkce použije hodnotu PRAVDA a hledá přibližně. Předpokládá, že je první sloupec seřazený vzestupně, a vrátí řádek s nejbližší nižší hodnotou. U neseřazeného ceníku tak dostanete cenu jiného produktu a žádnou chybovou hlášku. Sám bych nulu na konec psal vždy, i tam, kde se zdá zbytečná, protože chybný výsledek bez varování je horší než chyba, která je vidět.

Přibližná shoda má legitimní použití u pásem. Pokud se cena dopravy řídí hmotností balíku a tabulka uvádí dolní hranice pásem 0, 5, 10 a 20 kilogramů, najde přibližné hledání pro balík o 7 kilogramech správně řádek s hranicí 5.

Kde SVYHLEDAT naráží: směr hledání a číslo sloupce

Funkce hledá výhradně v prvním sloupci oblasti a vrací údaje jen napravo od něj. Je-li v ceníku název vlevo od kódu, SVYHLEDAT ho nenajde a sloupce by bylo nutné přeskupit.

Druhé omezení je zrádnější. Číslo sloupce je ve vzorci zapsané jako pevná hodnota, takže když někdo do ceníku vloží nový sloupec mezi kód a cenu, trojka začne ukazovat na jiný údaj. Vzorec se nerozbije viditelně, jen vrací něco jiného. Toto považuji za hlavní důvod, proč SVYHLEDAT nepoužívat v sešitech, které upravuje víc lidí.

Hledá se navíc vždy shora dolů a vrací první nalezený řádek. Opakuje-li se kód v ceníku dvakrát, druhý výskyt funkce nikdy neuvidí.

XLOOKUP: sloupec pro hledání a sloupec pro výsledek zvlášť

XLOOKUP dostává místo jedné oblasti a čísla sloupce dvě samostatné oblasti: kde hledat a odkud vracet. Stejná úloha se zapíše =XLOOKUP(A2; F:F; H:H). Výsledný sloupec může ležet vlevo i vpravo od hledaného a vložení dalšího sloupce do ceníku vzorec neovlivní, protože odkazy se posunou s daty.

Přesná shoda je výchozí, není ji třeba zadávat. Čtvrtý, nepovinný argument určuje, co se má vrátit, když hodnota nalezena není, takže odpadá obalování další funkcí. Další nepovinné argumenty dovolují hledat odspodu, což se hodí pro dohledání poslední objednávky zákazníka.

Háček je v dostupnosti. XLOOKUP je v Excelu pro Microsoft 365 a v Excelu 2021 a novějším, Excel 2019 a starší ho neznají. Umějí ho také Tabulky Google a novější verze LibreOffice Calc. Než ho v sešitu použijete, zeptal bych se, v čem soubor otevírají kolegové a odběratelé – ve starší verzi uvidí místo výsledků chyby.

INDEX a POZVYHLEDAT pro starší verze

Kde XLOOKUP není, nabízí stejnou pružnost dvojice starších funkcí. POZVYHLEDAT zjistí, na kolikátém řádku se hledaná hodnota nachází, a INDEX z jiného sloupce vrátí hodnotu na této pozici: =INDEX(H:H; POZVYHLEDAT(A2; F:F; 0)). Nula na konci opět znamená přesnou shodu. Kombinace hledá libovolným směrem a vložení sloupce jí nevadí, jen se hůř čte. Doporučil bych ji všude, kde sešit musí fungovat i ve starém Excelu.

Chyba #N/A: příčiny a ošetření

Když hledaná hodnota v tabulce není, vrátí všechny tři postupy chybu #N/A; česká verze Excelu ji zobrazuje jako #NENÍ_K_DISPOZICI. Často ale hodnota v tabulce je, jen se neshoduje přesně. Nejčastěji jde o mezeru na konci textu, kterou odstraní funkce PROČISTIT, nebo o číslo uložené na jedné straně jako číslo a na druhé jako text. Druhý případ vzniká hlavně při načítání exportů, proto se vyplatí znát pravidla importu souborů CSV.

Chybu lze nahradit vlastním výsledkem funkcí IFERROR, která se v českém Excelu jmenuje CHYBHODN: =CHYBHODN(SVYHLEDAT(A2; $F$2:$H$500; 3; 0); 0). Je to ale hrubý nástroj, protože skryje jakoukoli chybu včetně překlepu v odkazu na oblast. Užší je funkce IFNA, která zachytí pouze #N/A.

Chyby bych neskrýval dřív, než zjistíte, kolik jich je. Vyfiltrujte si ve sloupci s vyhledávacím vzorcem chybové hodnoty a projděte je; obvykle ukážou na produkty, které v ceníku chybějí, a to je informace, kterou potřebujete vidět. Stejně poslouží kontingenční tabulka postavená nad doplněnými daty, kde se nespárované řádky sejdou v jedné položce. Vyhledávání samo je jen jedním z kroků, kterými se ze seznamu v tabulkovém procesoru stává přehled.

Zdroje

• SVYHLEDAT (funkce), Microsoft Support
• XLOOKUP (funkce), Microsoft Support
• Vyhledání hodnot pomocí funkcí INDEX a POZVYHLEDAT, Microsoft Support
• Seznam funkcí Tabulek Google, Nápověda Editorů Dokumentů Google