XLOOKUP (funkcia)

Pomocou funkcie XLOOKUP môžete hľadať položky v tabuľke alebo rozsahu podľa riadka. Môžete napríklad vyhľadať cenu automobilového dielu podľa čísla dielu alebo zistiť meno zamestnanca na základe ID zamestnanca. Pomocou funkcie XLOOKUP môžete vyhľadať hľadaný výraz v jednom stĺpci a vrátiť výsledok z rovnakého riadka v inom stĺpci bez ohľadu na to, na ktorej strane sa nachádza stĺpec návratu.

Poznámka: Funkcia XLOOKUP nie je k dispozícii v Excel 2016 a Exceli 2019. Môže sa však vyskytnúť situácia pri používaní zošita v Excel 2016 alebo Exceli 2019 s funkciou XLOOKUP, ak ho vytvoril niekto iný pomocou novšej verzie Excelu.

Váš prehliadač nepodporuje video. Nainštalujte si Microsoft Silverlight, Adobe Flash Player alebo Internet Explorer 9.

Syntax

Funkcia XLOOKUP vyhľadá rozsah alebo pole a potom vráti položku zodpovedajúcu prvej nájdenej zhode. Ak žiadna zhoda neexistuje, funkcia XLOOKUP môže vrátiť najbližšiu (približnú) zhodu.

=XLOOKUP(vyhľadávaná_hodnota, pole_vyhľadávania, pole_vrátenia, [ak_sa_nenájde], [režim_zhody], [režim_vyhľadávania])

Argument	Popis
vyhľadávaná_hodnota Povinné*	Hodnota, ktorá sa má vyhľadať pre Ak sa vynechá, funkcia XLOOKUP vráti prázdne bunky, ktoré nájde v poli pole_vyhľadávania*.
pole_vyhľadávania Povinné	Pole alebo rozsah, ktorý chcete prehľadať
pole_vrátenia Povinné	Pole alebo rozsah na vrátenie
[ak_sa_nenájde] Voliteľné	Ak sa nenájde platná zhoda, vráti sa vami zadaný text [ak_sa_nenájde]. Ak sa nenájde platná zhoda a chýba [ak_sa_nenájde], vráti sa #NIE JE K DISPOZÍCII.
[režim_zhody] Voliteľné	Špecifikujte typ zhody: 0 – presná zhoda. Ak sa žiadna nenájde, vráti sa ##NIE JE K DISPOZÍCII. Toto je predvolené nastavenie. -1 – presná zhoda. Ak sa žiadna nenájde, vráti sa nasledujúca menšia položka. 1 – presná zhoda. Ak sa žiadna nenájde, vráti sa nasledujúca väčšia položka. 2 – Vyhľadáva zhodu zástupného znaku, kde *, ? a ~ má špeciálny význam.
[režim_vyhľadávania] Voliteľné	Zadajte režim vyhľadávania, ktorý sa má použiť: 1 – Vykoná hľadanie, ktoré sa spustí od prvej položky. Toto je predvolené nastavenie. -1 – Vykoná reverzné hľadanie, ktoré sa spustí od poslednej položky. 2 – Vykoná binárne vyhľadávanie, ktoré závisí od vzostupného zoradenia poľa pole_vyhľadávania. Ak nie je zoradené, vrátia sa neplatné výsledky. -2 – binárne vyhľadávanie, ktoré využíva zostupné zoradenie poľa pole_vyhľadávania. Ak nie je zoradené, vrátia sa neplatné výsledky.

Argument

Popis

vyhľadávaná_hodnota

Povinné*

Hodnota, ktorá sa má vyhľadať pre *Ak sa vynechá, funkcia XLOOKUP vráti prázdne bunky, ktoré nájde v poli pole_vyhľadávania.

pole_vyhľadávania

Povinné

Pole alebo rozsah, ktorý chcete prehľadať

pole_vrátenia

Povinné

Pole alebo rozsah na vrátenie

[ak_sa_nenájde]

Voliteľné

Ak sa nenájde platná zhoda, vráti sa vami zadaný text [ak_sa_nenájde].

Ak sa nenájde platná zhoda a chýba [ak_sa_nenájde], vráti sa #NIE JE K DISPOZÍCII.

[režim_zhody]

Voliteľné

Špecifikujte typ zhody:

0 – presná zhoda. Ak sa žiadna nenájde, vráti sa ##NIE JE K DISPOZÍCII. Toto je predvolené nastavenie.

-1 – presná zhoda. Ak sa žiadna nenájde, vráti sa nasledujúca menšia položka.

1 – presná zhoda. Ak sa žiadna nenájde, vráti sa nasledujúca väčšia položka.

2 – Vyhľadáva zhodu zástupného znaku, kde *, ? a ~ má špeciálny význam.

[režim_vyhľadávania]

Voliteľné

Zadajte režim vyhľadávania, ktorý sa má použiť:

1 – Vykoná hľadanie, ktoré sa spustí od prvej položky. Toto je predvolené nastavenie.

-1 – Vykoná reverzné hľadanie, ktoré sa spustí od poslednej položky.

2 – Vykoná binárne vyhľadávanie, ktoré závisí od vzostupného zoradenia poľa pole_vyhľadávania. Ak nie je zoradené, vrátia sa neplatné výsledky.

-2 – binárne vyhľadávanie, ktoré využíva zostupné zoradenie poľa pole_vyhľadávania. Ak nie je zoradené, vrátia sa neplatné výsledky.

Príklady

Príklad 1 používa funkciu XLOOKUP na vyhľadanie názvu krajiny v rozsahu a následné vrátenie telefónnej predvoľby krajiny. Obsahuje argumenty hodnota_vyhľadávania (bunka F2), pole_vyhľadávania (rozsah B2:B11) a pole_vrátenia (rozsah D2:D11). Neobsahuje argument typ_zhody, pretože funkcia XLOOKUP predvolene vytvára presnú zhodu.

Príklad funkcie XLOOKUP používanej na vrátenie mena zamestnanca a oddelenia na základe ID zamestnanca. Vzorec je =XLOOKUP(B2;B5:B14;C5:C14)

Poznámka: Funkcia XLOOKUP používa pole vyhľadávania a pole vrátenia, zatiaľ čo funkcia VLOOKUP používa jedno pole tabuľky, za ktorým nasleduje číslo indexu stĺpca. Ekvivalentný vzorec funkcie VLOOKUP by v tomto prípade bol: =VLOOKUP(F2;B2:D11;3;FALSE)

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

Príklad 2 vyhľadá informácie o zamestnancovi na základe čísla ID zamestnanca. Na rozdiel od funkcie VLOOKUP funkcia XLOOKUP môže vrátiť pole s viacerými položkami, takže jeden vzorec môže vrátiť meno aj oddelenie zamestnanca z buniek C5:D14.

Príklad funkcie XLOOKUP používanej na vrátenie mena zamestnanca a oddelenia na základe ID zamestnanca. Vzorec je: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

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

Príklad 3 pridá do predchádzajúceho príkladu argumentak_sa_nenájde.

Príklad funkcie XLOOKUP používanej na vrátenie mena zamestnanca a oddelenia na základe ID zamestnanca s argumentom ak_sa_nenájde. Vzorec je =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Zamestnanec sa nenašiel")

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

Príklad 4 zistí v stĺpci C osobný príjem zadaný v bunke E2 a vyhľadá zodpovedajúcu sadzbu dane v stĺpci B. Nastaví argument ak_sa_nenájde na vrátenie hodnoty 0 (nula), ak sa nič nenájde. Argument typ_zhody je nastavený na hodnotu 1, čo znamená, že funkcia vyhľadá presnú zhodu a ak ju nenájde, vráti nasledujúcu väčšiu položku. Nakoniec argument režim_vyhľadávania je nastavený na hodnotu 1, čo znamená, že funkcia bude vyhľadávať od prvej položky po poslednú.

Obrázok funkcie XLOOKUP používanej na vrátenie sadzby dane na základe maximálneho príjmu. Toto je približná zhoda. Vzorec je: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Poznámka: Stĺpec pole_vyhľadávania funkcie XARRAY je napravo od stĺpca pole_vrátenia, zatiaľ čo funkcia VLOOKUP môže hľadať iba zľava doprava.

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

Príklad 5 používa vnorenú funkciu XLOOKUP na vykonanie zvislej aj vodorovnej zhody. Najprv vyhľadá Hrubý zisk v stĺpci B, potom vyhľadá 1. štvrťrok v hornom riadku tabuľky (rozsah C5:F5) a nakoniec vráti hodnotu ich priesečníka. Je to podobné ako súčasné používanie funkcií INDEX a MATCH.

Tip: Funkciu HLOOKUP môžete tiež nahradiť funkciou XLOOKUP.

Obrázok funkcie XLOOKUP používanej na vrátenie vodorovných údajov z tabuľky vnorením 2 XLOOKUPs. Vzorec je: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

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

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

Príklad 6 používa funkciu SUM a dve vnorené funkcie XLOOKUP na sčítanie všetkých hodnôt medzi dvoma rozsahmi. V tomto prípade chceme sčítať hodnoty pre hrozno, banány a zahrnúť hrušky, ktoré sú medzi nimi.

Použitie funkcie XLOOKUP s funkciou SUM na súčet rozsahu hodnôt, ktoré spadajú medzi dva výbery

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

Ako to funguje? Funkcia XLOOKUP vráti rozsah, takže pri výpočte bude vzorec vyzerať takto: =SUM($E$7:$E$9). Ako to funguje môžete sami zistiť tak, že vyberiete bunku so vzorcom funkcie XLOOKUP, ktorý sa podobá tomuto, potom vyberiete položky Vzorce > Auditovanie vzorcov > Vyhodnotiť vzorec a potom výberom položky Vyhodnotiť prejdete výpočtom.

Poznámka: Za návrh tohto príkladu ďakujeme Billovi Jelenovi, MVP pre Microsoft Excel.

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

Pozrite tiež

Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.

XMATCH (funkcia)

Zoznam funkcií Excelu (podľa abecedy)

Zoznam funkcií Excelu (podľa kategórie)

Syntax

Príklady

Pozrite tiež

Potrebujete ďalšiu pomoc?

Chcete ďalšie možnosti?

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!