Funkce XLOOKUP

Funkce XLOOKUP slouží k vyhledání položek v tabulce nebo oblasti podle řádku. Podívejte se například na hodnotu automobilové části podle čísla dílu nebo najděte jméno zaměstnance na základě jeho ID zaměstnance. Pomocí funkce XLOOKUP můžete hledat hledaný termín v jednom sloupci a vracet výsledek ze stejného řádku v jiném sloupci bez ohledu na to, na které straně je vrácený sloupec.

Poznámka: Funkce XLOOKUP není k dispozici v Excel 2016 a Excelu 2019, ale můžete narazit na situaci použití sešitu v Excel 2016 nebo Excelu 2019 s funkcí XLOOKUP v něm vytvořenou někým jiným, kdo používá novější verzi Excelu.

Váš prohlížeč nepodporuje video. Nainstalujte si Microsoft Silverlight, Adobe Flash Player nebo Internet Explorer 9.

Syntaxe

Funkce XLOOKUP prohledá oblast nebo pole a vrátí položku odpovídající první shodě, kterou najde. Pokud neexistuje žádná shoda, může funkce XLOOKUP vrátit nejbližší (přibližnou) shodu.

=XLOOKUP(vyhledat_hodnota, vyhledat_pole, vrátit_pole [if_not_found], [match_mode], [search_mode])

Argument	Popis
co Povinné*	Hodnota, která se má vyhledat Pokud je vynechána, vrátí funkce XLOOKUP prázdné buňky, které najde v lookup_array*.
Prohledat: Povinný	Pole nebo oblast, které se mají vyhledat
vrátit_pole Povinný	Pole nebo oblast, které se mají vrátit
[if_not_found] Nepovinný	Pokud není nalezena platná shoda, vrátí zadaný text [if_not_found]. Pokud se nenajde platná shoda a chybí [if_not_found], vrátí #N/A.
[match_mode] Nepovinný	Zadejte typ shody: 0 – Přesná shoda. Pokud není nic nalezeno, vraťte #N/A. Toto je výchozí možnost. −1 – Přesná shoda. Pokud není nic nalezeno, vrátí následující menší položku. 1 – Přesná shoda. Pokud není nic nalezeno, vrátí následující větší položku. 2 – Shoda se zástupným znakem, kde znaky *, ? a ~ mají speciální význam.
[typ_hledání] Nepovinný	Zadejte režim vyhledávání, který se má použít: 1 – Provede vyhledávání počínaje první položkou. Toto je výchozí možnost. −1 – Provede zpětné vyhledávání počínaje poslední položkou. 2 – Provede binární vyhledávání, které závisí na lookup_array seřazené ve vzestupném pořadí. Pokud není seřazené, vrátí se neplatné výsledky. −2 – Provede binární vyhledávání, které závisí na lookup_array seřazené ve sestupném pořadí. Pokud není seřazené, vrátí se neplatné výsledky.

Argument

Popis

Povinné*

Hodnota, která se má vyhledat

*Pokud je vynechána, vrátí funkce XLOOKUP prázdné buňky, které najde v lookup_array.

Prohledat:

Povinný

Pole nebo oblast, které se mají vyhledat

vrátit_pole

Povinný

Pole nebo oblast, které se mají vrátit

[if_not_found]

Nepovinný

Pokud není nalezena platná shoda, vrátí zadaný text [if_not_found].

Pokud se nenajde platná shoda a chybí [if_not_found], vrátí #N/A.

[match_mode]

Nepovinný

Zadejte typ shody:

0 – Přesná shoda. Pokud není nic nalezeno, vraťte #N/A. Toto je výchozí možnost.

−1 – Přesná shoda. Pokud není nic nalezeno, vrátí následující menší položku.

1 – Přesná shoda. Pokud není nic nalezeno, vrátí následující větší položku.

2 – Shoda se zástupným znakem, kde znaky *, ? a ~ mají speciální význam.

[typ_hledání]

Nepovinný

Zadejte režim vyhledávání, který se má použít:

1 – Provede vyhledávání počínaje první položkou. Toto je výchozí možnost.

−1 – Provede zpětné vyhledávání počínaje poslední položkou.

2 – Provede binární vyhledávání, které závisí na lookup_array seřazené ve vzestupném pořadí. Pokud není seřazené, vrátí se neplatné výsledky.

−2 – Provede binární vyhledávání, které závisí na lookup_array seřazené ve sestupném pořadí. Pokud není seřazené, vrátí se neplatné výsledky.

Příklady

Příklad 1 používá funkci XLOOKUP k vyhledání názvu země v oblasti a vrátí její telefonní směrové číslo země. Zahrnuje argumenty vyhledat_hodnota (buňka F2), vyhledat_pole (oblast B2:B11) a vrátit_pole (oblast D2:D11). Neobsahuje argument match_mode, protože funkce XLOOKUP ve výchozím nastavení vytvoří přesnou shodu.

Příklad funkce XLOOKUP, která se používá k vrácení jména a oddělení zaměstnance na základě ID zaměstnance. Vzorec je =XLOOKUP(B2,B5:B14,C5:C14).

Poznámka: Funkce XLOOKUP používá vyhledávací pole a návratové pole, zatímco funkce VLOOKUP používá jedno pole tabulky následované číslem indexu sloupce. Ekvivalentní vzorec VLOOKUP v tomto případě by byl: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

Příklad 2 vyhledá informace o zaměstnancích na základě čísla ID zaměstnance. Na rozdíl od funkce VLOOKUP může funkce XLOOKUP vrátit pole s více položkami, takže jeden vzorec může vrátit jméno zaměstnance i oddělení z buněk C5:D14.

Příklad funkce XLOOKUP, která se používá k vrácení jména a oddělení zaměstnance na základě IDt zaměstnance. Vzorec je: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

Příklad 3 přidá do předchozího příkladu argument if_not_found.

Příklad funkce XLOOKUP, která se používá k vrácení jména a oddělení zaměstnance na základě ID zaměstnance s argumentem if_not_found. Vzorec je =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Employee not found")

———————————————————————————

Příklad 4 vyhledá ve sloupci C osobní příjem zadaný v buňce E2 a najde odpovídající sazbu daně ve sloupci B. Nastaví argument if_not_found, aby se vrátila 0 (nula), pokud se nic nenajde. Argument match_mode je nastavený na 1, což znamená, že funkce bude hledat přesnou shodu, a pokud ji nenajde, vrátí následující větší položku. Nakonec je argument search_mode nastavený na 1, což znamená, že funkce bude hledat od první do poslední položky.

Obrázek funkce XLOOKUP, která se používá k vrácení sazby daně na základě maximálního příjmu. Toto je přibližná shoda. Vzorec je: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Poznámka: vyhledat_pole funkce XARRAY je napravo od sloupce vrátit_pole, zatímco VLOOKUP může hledat pouze zleva doprava.

———————————————————————————

Příklad 5 používá vnořenou funkci XLOOKUP k provedení svislé i vodorovné shody. Nejprve vyhledá hrubý zisk ve sloupci B, pak vyhledá Qtr1 v horním řádku tabulky (oblast C5:F5) a nakonec vrátí hodnotu v jejich průsečíku. To se podobá společnému použití funkcí INDEX a MATCH.

Tip: Pomocí funkce XLOOKUP můžete také nahradit funkci HLOOKUP.

Obrázek funkce XLOOKUP, která slouží k vrácení vodorovných dat z tabulky vnořením 2 funkcí XLOOKUP. Vzorec je: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

Poznámka: Vzorec v buňkách D3:F3 je: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).

———————————————————————————

Příklad 6 používá funkci SUMa dvě vnořené funkce XLOOKUP k sečtení všech hodnot mezi dvěma oblastmi. V tomto případě chceme sečíst hodnoty pro hrozny, banány a zahrnout hrušky, které jsou mezi těmito dvěma.

Použití funkce XLOOKUP s funkcí SUM k sečtení oblasti hodnot, které spadají mezi dva výběry

Vzorec v buňce E3 je: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Jak to funguje? Funkce XLOOKUP vrátí oblast, takže po výpočtu vzorec bude vypadat takto: =SUM($E$7:$E$9). To, jak to funguje, můžete zjistit sami tak, že vyberete buňku se vzorcem XLOOKUP, který je podobný tomuto, a pak vyberete Vzorce > Auditování vzorců > Vyhodnotit vzoreca pak výběrem Vyhodnotit budete krokovat výpočet.

Poznámka: Za návrh tohoto příkladu děkujeme Billu Jelenovi, Microsoft Excel MVP.

———————————————————————————