XLOOKUP
Funkce XLOOKUP vyhledá v rozmezí zadanou hodnotu a vrátí hodnotu ze stejného řádku v jiném sloupci.
XLOOKUP(hledaná-hodnota; prohledávané-rozmezí; návratové-rozmezí; při-nenalezení; typ-shody; typ-hledání)
hledaná-hodnota: Hodnota, kterou chcete najít v prohledávaném-rozmezí. Argument hledaná-hodnota může obsahovat hodnotu libovolného typu nebo řetězec REGEX.
prohledávané-rozmezí: Buňky, které chcete prohledávat.
návratové-rozmezí: Buňky, jejichž hodnoty má funkce vracet.
při-nenalezení: Nepovinný argument obsahující zprávu, která se má zobrazit, pokud nebude nalezena shoda.
typ-shody: Nepovinný argument určující typ shody, kterou chcete hledat.
Přesná shoda nebo nejbližší menší hodnota (-1): Pokud funkce shodu nenajde, vrátí chybu.
Přesná shoda (0 nebo vynecháno): Pokud nenastane přesná shoda, vrátí se chyba.
Přesná shoda nebo nejbližší větší hodnota (1): Pokud funkce shodu nenajde, vrátí chybu.
Zástupné znaky (2): Znaky *, ? a ~ mají speciální význam. Výraz REGEX lze ve funkci XLOOKUP použít jen v režimu zástupných znaků.
typ-hledání: Nepovinný argument určující směr, kterým chcete rozmezí prohledávat.
Binární sestupné (-2): Binární hledání, které vyžaduje sestupně seřazené rozmezí, jinak vrátí chybu.
Od konce (-1): Prohledává rozmezí od konce směrem k začátku.
Od začátku (1 nebo vynecháno): Prohledává rozmezí od začátku směrem ke konci.
Binární vzestupné (2): Binární hledání, které vyžaduje vzestupně seřazené rozmezí, jinak vrátí chybu.
Poznámky
Pokud je hodnotou argumentu prohledávané-rozmezí nebo návratové-rozmezí odkaz na celý sloupec či řádek (např. „B“), budou automaticky ignorována záhlaví a zápatí.
Vrácení výsledků z pole zajistíte pomocí funkce INDEX v kombinaci s funkcí XLOOKUP.
Příklad |
|---|
Následující tabulka s názvem Výrobky obsahuje seznam výrobků a jejich atributů, jako je velikost a cena: |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | Výrobek | Délka (cm) | Šířka (cm) | Váha (kg) | Cena |
2 | Výrobek 1 | 16 | 17 | 10 | 82 jednotek |
3 | Výrobek 2 | 16 | 20 | 18 | 77 jednotek |
4 | Výrobek 3 | 11 | 11 | 15 | 88 jednotek |
5 | Výrobek 4 | 15 | 16 | 20 | 63 jednotek |
Vyhledávání pomocí funkce XLOOKUP |
|---|
Pomocí funkce XLOOKUP můžete do tabulky vložit vzorec, který vrátí libovolnou související hodnotu, a to tak, že nejprve zadáte název výrobku a pak sloupec s hodnotou, která se má vrátit. Pokud byste například chtěli vrátit šířku 1. výrobku ve výše uvedené tabulce, mohli byste použít následující vzorec, který vrátí 17 cm:  V tomto vzorci byly použity následující argumenty:
|
Nastavení řetězce při-nenalezení |
|---|
Chcete-li vyhledat konkrétní délku výrobku a vrátit jeho odpovídající šířku a řetězec, který se má vrátit, pokud není nalezena žádná shoda, můžete použít následující vzorec, který vrátí „Nenalezeno“:  V tomto vzorci se argument při-nenalezení používá k přesnějšímu hledání:
|
Vyhledání další nejbližší hodnoty |
|---|
Pomocí funkce XLOOKUP lze také provádět rozšířená vyhledávání na základě konkrétní hodnoty a hodnot jí blízkých. Změníte-li typ-shody z výše uvedeného vzorce, může se vrátit šířka, která odpovídá délce 13 cm, nebo nejbližší nejnižší hodnota. Níže uvedený vzorec vrátí šířku 11 cm:  V tomto vzorci jsou argumenty stejné jako výše. Aby se však změnil způsob vyhledávání v tabulce, je pro typ-shody použita jiná hodnota:
|
Změna pořadí vyhledávání |
|---|
V některých případech může být užitečné změnit pořadí vyhledávání v tabulce pomocí funkce XLOOKUP. Například ve výše uvedené tabulce jsou dva produkty o délce 16 cm, takže při hledání 16 cm ve sloupci Délka pomocí hledané-hodnoty a prohledávaného-rozmezí budou dvě potenciální shody. Pořadí vyhledávání můžete nastavit například pomocí následujícího vzorce, který vrátí 20 cm:  V tomto vzorci se argument typ-hledání používá k nastavení pořadí, v němž funkce XLOOKUP hledá shodu v tabulce:
|
Použití funkce XLOOKUP v kombinaci s jinými funkcemi |
|---|
Funkci XLOOKUP lze použít v kombinaci s jinými funkcemi, například s funkcí SUM. Můžete například použít níže uvedený vzorec a vrátit 247 jednotek, což je součet cen 1., 2. a 3. výrobku:  V tomto příkladu první funkce XLOOKUP hledá cenu 1. výrobku a druhá funkce XLOOKUP hledá cenu 3. výrobku. Dvojtečka (:) mezi funkcemi XLOOKUP naznačuje, že funkce SUM by měla vrátit nejen celkovou cenu 1. a 3. výrobku, ale také všechny hodnoty mezi nimi. |
V níže uvedeném vzorci je funkce XLOOKUP použita s funkcí REGEX pro vrácení 2. výrobku, což je první výrobek o šířce začínající číslicí 2.  V tomto příkladu je pro typ-shody použitý "zástupný znak (2)", aby se ve funkci REGEX použily zástupné znaky. |
Další příklady |
|---|
Máme následující tabulku: |
A | B | C | |
|---|---|---|---|
1 | Jméno | Věk | Plat |
2 | Anna | 35 | 71000 |
3 | Matěj | 27 | 81000 |
4 | Katka | 42 | 86000 |
5 | Sofie | 51 | 66000 |
6 | Karel | 28 | 52000 |
7 | Tom | 49 | 62000 |
8 | Áron | 63 | 89000 |
9 | Marie | 22 | 34000 |
10 | Alice | 29 | 52000 |
11 | Bruno | 35 | 52500 |
Vzorec =XLOOKUP(49;B2:B11;C2:C11) vrátí hodnotu „62000“, což je plat prvního 49letého zaměstnance. Vzorec =XLOOKUP(60000;C2:C11;B2:B11;"Nenalezeno") vrátí text „Nenalezeno“, protože plat 60 000 nemá žádný ze zaměstnanců. Vzorec =XLOOKUP(REGEX("^K.*"); A2:A11; B2:B11; NEPRAVDA; 2) vrátí hodnotu „42“, což je věk Katky – prvního zaměstnance v daném rozmezí, jehož jméno začíná na „K“. =INDEX(XLOOKUP(A2;A2:A11;B2:C11);2) vrátí hodnotu 71000, což je druhá hodnota z pole vráceného funkcí XLOOKUP. |