XLOOKUP függvény (XKERES függvény)

2019 végén kerül bevezetésre az XKERES függvény (angolul XLOOKUP) az Office 365 programcsomagot használók számára, korábbi verziókban sajnos nem
elérhető. Ez a függvény képes kiváltani a fenti keresőfüggvényeket. Egyszerűen ötvözi a fentiek pozitív tulajdonságait, és csak egy függvényt
kell megtanulnod hozzá!

Ha használod akár az FKERES, VKERES, vagy az INDEX és HOL.VAN kombinációt, akkor érdemes megismerned az XKERES függvényt, mellyel mindegyik fenti
függvényt kiválthatod!

A leírásban használt fájlt XKERES függvény példákkal.

Az XLOOKUP függvényt 2019 augusztusában jelentette be a Microsoft, a videóból és a leírásból megtudhatod, hogyan működik. A videó óta egy újab argumentummal bővült a függvény a ha_nincs_talalat (lásd videó alatt)

XKERES függvény előnyei

A függvény előnyei:

• Képes jobbról balra keresni
• A pontos egyezés az alapértelmezett, nem kell külön argumentumban pontosítanod
• Megadhatod, hogy mit írjon ki, ha nem található érték, nem a #HIÁNYZIK! hibát fogod látni
• Függőlegesen és vízszintesen is kereshetsz vele
• Tartományt is képes visszaadni
• Képes megkeresni az utolsó előfordulást
• Használhatsz benne karakterhelyettesítőket a részleges egyezéshez

XKERES függvény argumentumai

Az XKERES függvény, vagy magyarul XKERES függvény egyezéseket keres valamely tartományban vagy tömbben és egy második tartományból vagy tömbből
adja vissza a megfelelő elemet.

=XKERES(keresési_érték; keresési_tömb; visszaadandó_tömb;ha_nincs_találat]; [egyeztetési_mód]; [keresési_mód])

=XLOOKUP(lookup_value; lookup_array; return_array; [not_found];[match_mode]; [search_mode])

keresési_érték: az érték, amit meg kell keresni a tartományban

keresési_tömb: az a tartomány, amelyben meg kell keresni

visszaadandó_tömb: az a tartomány, amelyből az eredményt szeretném visszakapni

[ha_nincs_találat]: opcionális

[egyeztetési_mód]: opcionális, az alapértelmezett a 0

• 0 a pontos egyezéshez
• -1 a pontos egyezés vagy a következő kisebb elem
• 1 a pontos egyezés vagy a következő nagyobb elem
• 2 a helyettesítő karakter

[keresési_mód]: opcionális, alapértelmezett az 1

• 1 fentről lefelé kereséshez
• -1 lentről felfelé kereséshez
• 2 bináris keresés növekvő sorrendben (ez lehetővé teszi, hogy helyettesítő karaktert használj a keresési értékben)
• -2 bináris keresés csökkenő sorrendben (ez lehetővé teszi, hogy helyettesítő karaktert használj a keresési értékben)

XKERES példa

Adott az alábbi táblázat, szállító, termékkód, kategória és eladási ár. Bizonyos termékkódokhoz keresem a szállítót. Az FKERES (VLOOKUP) függvénnyel ez nehézkes, mert ez a függvény nem tud balról eredményt visszaadni. Vagy átrendezzük a táblázatot úgy, hogy a termékkód megelőzze a szállítót, vagy használhatunk másik függvényt, az INDEX és a HOL.VAN függvények kombinációját.

Az XKERES első argumentumában meg kell adni, hogy mit kell megkeresni.

=XKERES(F6;

Második argumentum az az oszlop, amelyikben ezt az értéket meg fogja találni.

=XKERES(F6;$B$6:$B$29;

Harmadik argumentum az az oszlop, amelyből az eredményt szeretnéd megkapni.

=XKERES(F6;$B$6:$B$29;$A$6:$A$29)

A további argumentumokat most elhagyjuk, az alapértelmezett értékeik megfelelők ehhez a kereséshez.

Az eredmények lefelé másolva:

Ha nincs találat

Egészítsük ki a függvényt a következő argumentummal, mi történjen, ha nincs találat. Ide megadhatunk egy egyéni szöveget, például azt, hogy „nincs
ilyen termék”. Idézőjelek között kell megadni, mivel szöveges tartalom. A meglévő függvényt az első cellában javítsd, hogy tudjuk majd lefelé
másolni.

A függvény:

=XKERES(F6;$B$6:$B$29;$A$6:$A$29;”Nincs ilyen termék”)

Másolás után az eredmény:

Közelítőleges egyezés

Közelítőleges egyezés megtalálására is tudjuk használni. Ehhez a korábbi FKERES példát fogom használni. A [ha_nincs_találat] és a [egyeztetési_mód] argumentumokat figyelmen kívül hagyhatjuk ennél a példánál.

Az első argumentum a végösszeg, a második argumentum az E oszlop, itt fogja megtalálni ezt az értéket (közelítőleges egyezéssel), harmadik argumentum a kedvezmény oszlopa, ezt szeretném eredményül kapni. Negyedik argumentumot üresen hagyom, az ötödik pedig a -1.

=XKERES(keresési_érték; keresési_tömb; visszaadandó_tömb; [ha_nincs_találat]; [egyeztetési_mód]; [keresési_mód])

[egyeztetési_mód]: opcionális, az alapértelmezett a 0

• 0 a pontos egyezéshez
• -1 a pontos egyezés vagy a következő kisebb elem
• 1 a pontos egyezés vagy a következő nagyobb elem
• 2 a helyettesítő karakter

A függvény: =XKERES(B34;$E$34:$E$37;$F$34:$F$37;;-1)

Az eredmények:

Vízszintes keresés

A VKERES függvényt is ki tudod váltani az XKERES függvénnyel, vagyis vízszintesen is tudsz keresést végezni. A lenti példában arra vagyok
kíváncsi, hogy mennyi darabot adtak el Székesfehérváron. A VKERES példáján keresztül:

=XKERES(A60;A53:E53;A55:E55)

Annyi a különbség, hogy itt nem kell megadni az egész táblát, csak a keresési tömböt és a visszaadandó tömböt (ahogy az FKERESHEZ hasonlítva is
láttuk)

Tartomány eredményként

Egyetlen cella helyett tartományt is képes visszaadni eredményül.

Ehhez annyi a teendőd, hogy a visszaadandó tömbnél nem egy oszlopot jelölsz ki, hanem annyit, amennyit szeretnél visszakapni. A példában a termékkódhoz
szeretném megadni a Szállítót, a Kategóriát és az eladási árat is. Ezt eddig vagy annyiszor írtuk meg, ahány oszlop volt, vagy kis odafigyeléssel
úgy is meg lehetett írni, hogy jobbra tudjuk másolni.

Ez viszont most lényegesen egyszerűsödik az XKRES függvénnyel.

Kitölti mindhárom oszlopot a példában. Ezek után elég az első cellát másolnod lefelé.

Utolsó előfordulás megtalálása

Az XKERES függvénnyel képes vagy az utolsó előfordulás megtalálására, be tudod ugyanis állítani, hogy ne fentről lefelé, hanem lentről felfelé
indítsa a keresést.

Az alábbi példában időrendben szerepelnek az eladások három termék esetében, A, B és C. Arra vagyok kíváncsi, hogy mikor volt az utolsó
értékesítés az egyes termékekből. =XKERES(keresési_érték; keresési_tömb; visszaadandó_tömb; [ha_nincs_találat]; [egyeztetési_mód]; [keresési_mód])

A [ha_nincs_találat]; [egyeztetési_mód] elhagyható.

[keresési_mód]: opcionális, alapértelmezett az 1

• 1 fentről lefelé kereséshez
• -1 lentről felfelé kereséshez
• 2 bináris keresés növekvő sorrendben (ez lehetővé teszi, hogy helyettesítő
  karaktert használj a keresési értékben)
• -2 bináris keresés csökkenő sorrendben (ez lehetővé teszi, hogy helyettesítő karaktert használj a keresési értékben)

=XKERES(E94;$B$93:$B$112;$A$93:$A$112;;;-1)

Az eredmények:

XKERES – Karakterhelyettesítő használata

Karakterhelyettesítőket is használhatsz a függvényben, ami nagyon szuper! Például itt Péterre keresek rá, a lakhelyére. Két Péter is szerepel a
listában, az elsőt fogja eredményül adni.

=XKERES(keresési_érték; keresési_tömb; visszaadandó_tömb; [ha_nincs_találat]; [egyeztetési_mód]; [keresési_mód])

Itt a keresési módot hagyjuk el, a ha nincs találat argumentumhoz nem írunk semmit, és az egyeztetési mód a 2.

=XKERES(D117;A117:A120;B117:B120;;2)

Az eredmény Eger.

Összefoglaló

Az XKERES függvény nagyon jó újítás, de csak az Office 365 előfizetésben érhető el! Ha ilyened van, javaslom, hogy ezt gyakorold be a többi függvény helyett, mert tényleg sokoldalú.

Ha régebbi verziód van, akkor sem érdemes kihagyni a keresőfüggvényeket, rengeteg lehetőség álla rendelkezésedre a keresésekhez. Az INDEX és HOL.VAN nehezebbnek tűnik, de érdemes elsajátítanod, mert nagyon sok helyzetben fogod tudni alkalmazni.

Ha pedig elegendő neked a munkádban egy FKERES, akkor azt gyakorold be úgy, hogy álmodból felkeltve is tudd, sokat fog segíteni!

Köszönöm, hogy időt szántál a függvények megismerésére és megtiszteltél bizalmaddal!

Jó gyakorlást kívánok!