Funkce XLOOKUP je poměrně nová funkce, dostupná v MS Office 365. Funkce není přeložena, proto ji v české i anglické verzi Excelu najdete pod stejným názvem XLOOKUP.
V základu je podobná funkci SVYHLEDAT, o které se více dozvíte v tomto článku, má oproti ní ale pár vychytávek, takže věřím, že si také najde své oblíbence 
. Oproti funkci SVYHLEDAT je podle mě:
- jednodušší na zadávání (má jen 3 povinné argumenty),
- snazší na pochopení,
- zároveň nabízí několik rozšíření v podobě 3 nepovinných argumentů, které jsme s SVYHLEDAT museli složitěji obcházet.
Funkce slouží k vyhledání položek v tabulce a vrácení hodnoty z jiného sloupce. Na rozdíl od funkce SYVHLEDAT, ale nemusí být hledaná hodnota VPRAVO od hodnoty, která propojuje obě tabulky! Je tedy jedno, na které straně je hledaný sloupec a jak jsou sloupce uspořádané. Navíc funkce umí hledat hodnotu i horizontálně ve sloupcích, díky tomu nahrazuje i další vyhledávací funkce (příklad 6).
Další velkou výhodou je, že ve funkci je již zakomponována funkce IFERROR pro ošetření toho, že v některém řádku nebude hodnota dohledána. V ideálním světe a databázi by se nám tohle moc dít nemělo, ale řekněme si na rovinu, že v reálném světě je kombinace SVYHLEDAT a IFERROR úplně běžná, ne-li nutná, a proto nás i Microsoft touto vychytávkou přímo ve funkci vybavil (příklad 2).
Jak tedy funkci vytvoříme?
Funkce obsahuje 3 povinné a 3 volitelné argumenty:
- CO
- povinný argument
- Co hledám?
- PROHLEDAT
- povinný argument
- Označím sloupec, ve kterém je hledaná hodnota v druhé/prohledávané tabulce.
- VRÁTIT
- povinný argument
- Označím sloupec, ze kterého chci vrátit hodnotu.
- POZOR! Můžeme zde označit a vrátit současně i více sloupců (příklad 3).
- Pokud_nenalezeno
- nepovinný argument
- Co má funkce vrátit, pokud nic nenajde (může to být hodnota, text, chybová hláška …). Zde je zakomponována již zmiňovaná funkce IFERROR.
- Pokud tento argument nezadáme, funkce vrátí v případě nenalezení hodnoty chybovou hlášku #NENÍ_K_DISPOZICI.
- Režim_shody (příklad 4)
- nepovinný argument
- Známe z funkce SVYHLEDAT. Většinou (i defaultně) je nastavena přesná shoda = 0.
- Typy shod:
- 0 = přesná shoda
- -1 = nejbližší nižší číslo
- 1 = nejbližší vyšší číslo
- 2 = shoda se zástupnými znaky (příklad 5):
- * = libovolný počet znaků
- ? = libovolný jeden znak
- Režim _vyhledávání
- nepovinný argument
- V jakém směru budeme vyhledávat (shora nebo odspodu).
- Defaultně je nastaveno od první po poslední hodnotu.
- Typy režimů:
- 1 = vyhledává od začátku
- -1 = vyhledává od konce
- 2 = vzestupně seřazené
- -2 = sestupně seřazené
Kouzlo je především v argumentu PROHLEDAT a VRÁTIT. Na rozdíl od funkce SVYHLEDAT(VLOOKUP) nemusíme označovat v argumentu PROHLEDAT celou tabulku, nemusíme řešit pořadí sloupců a ani počítat kolikátý sloupec chceme vrátit . Jednoduše v XLOOKUP nejprve označíme společný sloupec pro obě tabulky a potom sloupec, ze kterého chceme hodnotu vrátit.
Připravila jsem si pro vás i několik praktických příkladů, které najdete ve video návodu níže:
1. Z tabulky níže doplňte pomocí XLOOKUP ID produktu.
Stačí nám vyplnit první 3 argumenty a máme výsledek.
2. Přímo ve funkci ošetřete chybovou hlášku.
Postel v tabulce nemáme. Pokud nechceme zobrazovat chybovou hlášku #NENÍ_K_DISPOZICI, nastavíme 4. argument POKUD NENALEZENO. Toto je praktická ukázka nahrazení funkce IFERROR, kterou bychom museli použít u funkce SVYHLEDAT.
3. Vraťte současně oba sloupce: ID i Cenu za kus.
V argumentu VRÁTIT označíme OBA SLOUPCE. Ve výsledné tabulce stačí napsat funkci do prvního z nich (v našem příkladu ID) a automaticky se funkce vloží i do dalších sloupců.
4. Do sloupce Množstevní sleva doplňte výši slevy podle počtu kusů (nejbližší nižší číslo).
Všimněme si, že hledaná hodnota POČET KUSŮ je VLEVO od % slevy, kterou funkcí XLOOKUP vracíme. To bychom s funkcí SVYHLEDAT nedokázali.
Pokud chceme vrátit nejbližší nižší číslo, nezapomeneme nastavit 5. argument REŽIM SHODY = -1.
5. Použijte zástupný znak, abychom našli v tabulce Česko = Česká republika.
Pokud používáme zástupné znaky, musíme nastavit 5. argument REŽIM SHODY =2. V tomto příklady je předchozí argument POKUD NENALAZENO prázdný.
6. Pomocí XLOOKUP najděte v tabulce vlevo počet kusů pro vybranou kombinaci (Novák + komoda) :
Druhá XLOOKUP funkce je argumentem VRÁTIT první XLOOKUP funkce. V tomto příkladu jsme nahradili kombinaci funkcí POZVYHLEDAT a INDEX funkcí XLOOKUP.
Když si to ještě jednou shrneme – proč se naučit používat XLOOKUP?
- Nezáleží na pořadí sloupců – nemusíme tabulku upravovat.
- Nemusíme zvlášť ošetřovat funkcí IFERROR.
- Můžeme vrátit více sloupců současně.
- Funkce umí pracovat se zástupnými znaky (například * a ?).
- Funkce umí hledat i horizontálně -> můžeme s ní nahradit i dříve používanou kombinaci funkcí INDEX a POZVYHLEDAT.