Už jsem hodně dlouho nenapsal nic k Excelu a dnes jsem si vybral funkci XLOOKUP, kterou tu ještě ostudně nemám popsanou. Funkce byla oficiálně uvedena v září 2019 nejprve ve verzích Office 365 a postupně se stala standardní součástí Excelu i ve starších verzích. Hlavním důvodem zavedení XLOOKUP je nahrazení zastaralých funkcí VLOOKUP a HLOOKUP.
Stejně jako její předchůdci slouží XLOOKUP pro prohledávaní dat v tabulkách s tím, že nová funkce je o parník lepší než její předchůdci – a to jak z hlediska zjednodušení syntaxe a použitelnosti, tak z hlediska výkonu. Mezi ty nejvýznamější výhody patří třeba to, že nově nemusíme data před použitím funkce seřadit, aby funkce správně pracovala – nová funkce funguje obousměrně (umí hledat zleva doprava i zprava doleva), máte méně povinných parametrů, atd (více níže v kapitole Shrnutí výhod XLOOKUP). Pojďme ale postupně..
Syntaxe funkce XLOOKUP a parametry
Základní syntaxe funkce XLOOKUP jsou níže. Hodnoty v hranatých závorkách jsou nepovinné – to znamená, že je funkce jednodušší a povinné jsou pouze 3 argumenty: 1
=XLOOKUP(hledaná_hodnota; hledat_ve_sloupci_nebo_řádku; vrátit_ze_sloupce_nebo_řádku; [pokud_nenalezeno]; [režim_shody]; [režim_hledání])Vysvětlení parametrů funkce
a) Povinné
- hledaná_hodnota – hodnota, kterou funkce hledá – tj. nějaká buňka
- hledat_ve_sloupci_nebo_řádku – oblast, ve které se hledaná hodnota vyhledává. Můžeme hledat zleva doprava i zprava doleva. Na pořadí nezáleží
- vrátit_ze_sloupce_nebo_řádku – oblast, ze které se vrací odpovídající hodnota
b) Nepovinné – užitečné pro určité scénáře, úspora času a velikosti vzorce
- pokud_nenalezeno – volitelný parametr pro definování návratu při nenalezení hodnoty (funkci není potřeba obalovat ISERROR jako u předchůdců, ale dá se použít tento parametr =>zvyšuje přehlednost)
- režim_shody – volitelný parametr pro určení typu shody (přesná, přibližná). Tento parametr má i předchůdce, ale zde je nepovinný. V případě, že není vyplněn, tak se má za to, že se hledá přesná shoda.
- režim_hledání – volitelný parametr pro určení směru vyhledávání. Výhozí nastavení (pokud není vyplněno) je hledání od 1 sloupce nebo řádku k poslednímu. Máte možnost to však ovlivnit.
Shrnutí výhod funkce XLOOKUP vs VLOOKUP/HLOOKUP – 1 funkce vládne všem
XLOOKUP umožňuje jednoduché vyhledávání hodnot v řádcích i sloupcích tabulek. Dříve bylo pro hledání hodnot ve sloupcích nebo řádcích potřeba ovládat 2 funkce – VLOOKUP a HLOOKUP a mezi nimi si vybírat podle toho, jestli hledáme v řádcích nebo sloupcích. Nyní už to není potřeba a vše zvládneme pouze 1 funkcí.
Hlavní výhody XLOOKUP oproti VLOOKUP a HLOOKUP tedy zahrnují:
- Přesná i přibližná shoda: XLOOKUP umožňuje vyhledávat přesnou hodnotu nebo nejbližší hodnotu (vyšší či nižší) bez nutnosti seřazení dat. Funkce VLOOKUP/HLOOKUP podporuje přibližnou shodu pouze při seřazených datech a vyžaduje správně nastavený argument typu shody.
- Hledání hodnot zleva doprava i zprava doleva: Na rozdíl od VLOOKUP, který vyhledává pouze zleva doprava, XLOOKUP umožňuje vyhledávání v libovolném směru. To usnadňuje práci s tabulkami, kde sloupce nejsou uspořádány podle logického pořadí, a eliminuje nutnost přeuspořádání dat.
- Náhrada chybových hodnot vlastními zprávami nebo čísly: XLOOKUP má parametr pokud_nenalezeno, který umožňuje definovat vlastní výstup v případě, že hledaná hodnota neexistuje. U VLOOKUP a HLOOKUP je standardní kombinovat funkci s IFERROR nebo IFNA, což zvyšuje složitost vzorců a Excel se stává méně přehledný.
- Jednoduchá kombinace s dalšími funkcemi pro více kritérií: XLOOKUP lze efektivně kombinovat s funkcemi jako IF, FILTER nebo INDEX, což umožňuje vyhledávání na základě více kritérií. U tradičních funkcí by takové kombinace byly složitější a méně přehledné.
Díky novému upgradu se v XLOOKUP výrazně zjednodušuje vyhledávání dat a minimalizuje riziko chyby při práci s rozsáhlými nebo dynamickými tabulkami.
Příklad využití XLOOKUP v Excelu – 3 úkoly a různé situace
Používání funkce si ukážeme na příkladu viz screenshot. Je to jednoduchý seznam zákazníků, kde jedinečným identifikátorem zákazníka je CustomerID, podle kterého budeme prohledávat tabulku.
Všimněte si, že sloupec CustomerID (identifikátor zákazníka) dáváme až na konec – abych ukázel, že XLOOKUP umí hledat zprava doleva (narozdíl od VLOOKUP)
Máme 3 úkoly:
- Najít e-mail zákazníka CustomerID = 164
- Najít CompanyName zákazníka s CustomerID = 99999 a pokud neexistuje, tak zobrazit vlastní zprávu “!! Zákazník neexistuje !!”
- Ukázat první a poslední email zákazníka podle CompanyName = “Friendly Bike Shop” (tato společnost má 4 zákazníky)
Tohle je asi nejjednodušší a současně nejběžnější situace – prohledáváme tabulku a chceme vrátit hodnotu nějakého sloupce (EmailAddress) podle jiného sloupce (CustomerId). Zde procvičujeme základní syntaxi.
V druhém úkolu si procvičíme situaci, kdy hledáme hodnotu (CustomerID = 99999), která se v datech vůbec nevyskytuje. V takovém případě chceme ošetřit návratovou hodnotu a zobrazit vlastní zprávu. Ošetřování N/A hodnot patří také mezi nejčastější scénáře.
Tohle naopak nebude scénář, který budete často potřebovat, ale dá se na něm hezky ukázat co nová funkce umí. Zde budeme procvičovat poslední argument řežimu hledání (od začátku vs od konce).
Řešení vidíme na screenshotu. Pro jistotu jsem zafiltroval tabulku na CompanyName = Friendly Bike Shop a můžeme si zkontrolovat, že pomocí posledního argumentu jsme schopni ovládat to, že funkce prohledává buď od začátku nebo od konce. v prvním případě nám vrátila první hodnotu na kterou narazila shora a v druhém případě první hodnotu na kterou narazila zespod.
Rekapitulace postupů k používání funkce XLOOKUP, Excel ke stažení
- Zajistit, aby oblasti hledání a vracení hodnot měly stejnou délku
- Kombinovat XLOOKUP s funkcemi ISNA nebo IFERROR pro kontrolu chyb
- Pro dynamické seznamy využívat pojmenované oblasti.
- XLOOKUP podobně jako starší funkce vrací první shodu, kterou najde (pokud je v hledané oblasti více stejných hodnot)
Funkce XLOOKUP je robustní a flexibilní nástroj pro profesionální práci s daty v Excelu. Umožňuje efektivní vyhledávání hodnot, nahrazení chybových výstupů a práci s více kritérii.
Excel se zdrojovými daty a všemi úkoly si můžete stáhnout >> XLOOKUP_procvicovani_ukoly.xlsx
Reference
- Microsoft dokumentace, XLOOKUP function [on-line]. [cit. 2025-11-25]. Dostupné z WWW: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929