Funkce XLOOKUP

Funkce XLOOKUP je zařazena v kategorii Vyhledávací (Lookup).

Dostupnost: Excel pro Microsoft 365, Excel on-line

Využijete ji v tabulce, ve které hodnoty z jednoho sloupce určují každý řádek tabulky (např. v tabulce knih je každá kniha určena svým ISBN kódem) a vy z takové tabulky potřebujete získat údaje o konkrétní knize, např. její název.

Potřebujete-li funkci rychle použít, klikněte sem.

Porovnání s SVYHLEDAT/VLOOKUP

Předchůdkyně XLOOKUP je funkce VLOOKUP, která se v českém Excelu jmenuje SVYHLEDAT.

  • Lze získávat data z libovolného sloupce tabulky, tedy i ze sloupců nalevo od prohledávaného sloupce.
    (SVYHLEDAT prohledává první sloupec tabulky a umí vracet hodnoty pouze z těch sloupců, které jsou napravo od prohledávaného sloupce).
  • Můžete nastavit směr hledání: zda se má tabulka prohledávat od prvního nebo od posledního řádku.
  • Funkce nabízí možnost ošetřit stav, kdy hodnota nebyla nalezena.
    (Pokud funkce SVYHLEDAT nenajde zadanou hodnotu, zobrazí chybové hlášení. To bylo nutné ošetřit funkcí IFERROR. XLOOKUP dává možnost zadat hodnotu, která bude v buňce zobrazena v případě, že zadaná hodnota nebyla nalezena).
  • Možnost použít přesně nalezenou hodnotu nebo hodnotu nejbližší: XLOOKUP nabízí možnosti "Najít přesně nebo chybové hlášení", "Najít přesně nebo vzít nejbližší nižší hodnotu" a "Najít přesně nebo vzít nejbližší vyšší hodnotu" (funkce SVYHLEDAT nabízí pouze první dvě možnosti).
    Řazení dat: Bez ohledu na to, kterou z uvedených možností zvolíte, nemusíte řešit to, zda je tabulka seřazena podle prohledávaného sloupce.
    (Funkce SVYHLEDAT vyžadovala v takovém případě seřazení tabulky podle prohledávaného sloupce).
  • Možnosti vyhledávání:
    • Funkce XLOOKUP nabízí možnost prohledávat tabulku od prvního nebo od posledního řádku.
    • Funkce XLOOKUP nabízí možnost použít zástupné znaky * a ? při vyhledávání textů.
      (Funkce SVYHLEDAT nenabízí možnosti zástupných znaků a tabulku prohledává pouze od prvního řádku).
    • Při práci s velkými tabulkami můžete zvolit způsob prohledávání tabulky: výchozí nastavení nevyžaduje seřazení dat v tabulce a funkce prochází postupně jeden řádek po druhém. Zvolíte-li binární vyhledávání, musíte tabulku seřadit podle prohledávaného sloupce - hledání bude výrazně rychlejší.

Pro základní vysvětlení použijeme tabulku knih:

Ukázková tabulka

Rychlé použití funkce

Úkol: pro ISBN 21-987-62 vyhledejte název knihy.

Řešení: vyplňte tři povinné parametry funkce XLOOKUP(Co;Prohledat;Vrátit)

Co: Zadejte hledanou hodnotu, tedy 21-987-62. Můžete použít odkaz na buňku, ve které je hledaná hodnota zadaná.

Prohledat: Zadejte jednosloupcovou oblast, ve které má XLOOKUP najít zadanou hodnotu, tedy B2:B7

Vrátit: Zadejte jednosloupcovou oblast, ze které funkce načte požadovanou hodnotu, tedy A2:A7.

Důležité: Oblasti Prohledat a Vrátit musí začínat stejným řádkem (zde řádek č. 2) a končit stejným řádkem (zde řádek č. 7). Porušení tohoto pravidla vyvolá chybové hlášení.

Výsledek: Najde-li funkce zadané ISBN, vrátí název knihy. Nebude-li ISBN nalezeno, vrátí funkce chybové hlášení.

Zadání parametrů funkce XLOOKUP

Parametry funce XLOOKUP

XLOOKUP(Co;Prohledat;Vrátit;Pokud_Nenalezeno;Režim_shody;Režim_vyhledávání)

Co: Povinný parametr. Hodota, která má být v tabulce nalezena

Prohledat: Povinný parametr. Sloupec tabulky, ve které funkce hledá zadanou hodnotu.  Oblast nezahrnuje záhlaví tabulky.

Vrátit: Povinný parametr. Sloupec tabulky, ze kterého funkce načítá požadovanou hodnotu.
Důležité: Oba parametry (Prohledat i Vrátit) zadáváte jako oblasti. Obě oblasti musí začínat na stejném řádku a končit na stejném řádku. Není-li tomu tak, vrátí funkce chybové hlášení #HODNOTA.

Další parametry jsou nepovinné. Funkce XLOOKUP má celkem šest parametrů, ale v okně nástroje pro vložení funkce jich vidíte pouze pět. Pro zobrazení pole pro zadání šestého parametru použijte posuvník na pravé straně okna.

Pokud_nenalezeno: Nenalezne-li funkce XLOOKUP zadanou hodnotu, vrátí chybové hlášení #NENÍ_K_DISPOZICI nebo N/A. Zadáte-li hodnotu parametru Pokud_nenalezeno, funkce tuto hodnotu vrátí v případě, že hodnota zadaná do parametru Co nebyla nalezena.

Typ_shody: Zadává způsob, kterým funkce určuje, zda byla hodnota nalezena. Do tohoto parametru můžete zadat jednu z hodnot -1, 0, 12. Nezadáte-li tento parametr, Excel použije hodnotu 1.

-1: Je-li přesná shoda mezi zadanou a nalezenou hodnotou, použije Excel daný řádek tabulky. Není-li v tabulce nalezena odpovídající hodnota, Excel použije nejbližší menší hodnotu v prohledávané oblasti.

0: musí být nalezena hodnota, která se přesně shoduje se zadanou hodnotou. Není-li taková hodnota nalezena, funkce vrátí buď chybové hlášení nebo hodnotu zadanou v parametru Pokud_nenalezeno (je-li daný parametr vyplněn).

1: Je-li přesná shoda mezi zadanou a nalezenou hodnotou, použije Excel daný řádek tabulky. Není-li v tabulce nalezena odpovídající hodnota, Excel použije nejbližší vyšší hodnotu v prohledávané oblasti.

2: Vyhledáváte-li textovou hodnotu, je možné použít zástupné znaky * a ?.

Přesná shoda: U textů Excel nerozlišuje VELKÁ a malá písmena. Rozlišuje diakritiku: Plzeň se nerovná Plzen. Rozlišuje počty mezer a mezery na začátku a na konci textu: "Karlovy Vary" není totéž co "Karlovy  Vary" nebo "Karlovy Vary ".

Režim_vyhledávání: Parametr určuje, jak funkce prohledává tabulku. Do tohoto parametru můžete zadat jednu z hodnot -2, -1, 12. Nezadáte-li tento parametr, Excel použije hodnotu 1.

-1: Excel prohledává tabulku od posledního řádku. Tabulka nemusí být seřazena.

1: Excel prohledává tabulku od prvního řádku. Tabulka nemusí být seřazena.

-2: Excel prohledává tabulku od posledního řádku binárním vyhledáváním. Tabulka musí být seřazena.

2: Excel prohledává tabulku od prvního řádku binárním vyhledáváním. Tabulka musí být seřazena.

Příklady použití

Sešit s popsanými příklady stáhněte tady.

Ošetření stavu, kdy funkce nenalezla zadanou hodnotu

Zadání: Zjistěte cenu knihy s kódem ISBN 21-988-62. Zadá-li uživatel kód, který v tabulce není, zobrazí vzorec výsledek ISBN neexistuje.

Řešení: Vyplňte první tři parametry funkce. Do nepovinného parametru Pokud_neexistuje zapište text ISBN neexistuje.

Zadání parametrů funkce - ošetření chyby

Použití zástupných znaků

Zadání: Zjistěte název první knihy autora, jehož příjmení začíná písmenem V.

Řešení: Vyplňte první tři parametry funkce. Do nepovinného parametru Režim_shody zapište číslo 2. Jako hledanou hodnotu zadejte V*.

Zadaní parametrů - použití zást. znaků

inPage - webové stránky, doménawebhosting snadno.