S funkcijo XLOOKUP lahko poiščete elemente v tabeli ali obsegu po vrsticah. Poiščite na primer ceno avtomobilskega dela po številki dela ali pa poiščite ime zaposlenega glede na ID zaposlenega. S funkcijo XLOOKUP lahko iskani izraz poiščete v enem stolpcu in dobite rezultat iz iste vrstice v drugem stolpcu, ne glede na to, na kateri strani je vrnjen stolpec.
Opomba: Funkcija XLOOKUP ni na voljo v Excel 2016 in Excel 2019. Lahko pa pride do situacije uporabe delovnega zvezka v programu Excel 2016 ali Excel 2019 s funkcijo XLOOKUP v njej, če ga je ustvaril drug uporabnik z novejšo različico Excela.
Sintaksa
Funkcija XLOOKUP išče v obsegu ali matriki in nato vrne element, ki ustreza prvemu najdenega ujemanja. Če ujemanje ne obstaja, lahko funkcija XLOOKUP vrne najbližje (približno) ujemanje.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
|
Argument |
Opis |
|---|---|
|
iskana_vrednost Zahteva* |
Vrednost, ki jo želite poiskati *Če ga izpustite, vrne XLOOKUP prazne celice, ki jih najde v lookup_array. |
|
matrika_iskanja Obvezno |
Matrika ali obseg za iskanje |
|
return_array Obvezno |
Matrika ali obseg, ki ga želite vrniti |
|
[if_not_found] Izbirno |
Če veljavnega ujemanja ni mogoče najti, vrnite besedilo [if_not_found], ki ga vrnete. Če veljavnega ujemanja ni mogoče najti in če manjka [if_not_found], funkcija #N/V . |
|
[match_mode] Izbirno |
Navedite vrsto ujemanja: 0 – Natančno ujemanje. Če ga ni mogoče najti, #N/V. To je privzeta nastavitev. -1- Natančno ujemanje. Če ga ni bilo mogoče najti, vrnite naslednji manjši element. 1 – Natančno ujemanje. Če ni najdeno noben element, vrni naslednji večji element. 2 – Nadomestni znak se ujema s posebnim pomenom *, ? in ~ . |
|
[search_mode] Izbirno |
Določite način iskanja, ki ga želite uporabiti: 1 – Izvedite iskanje, ki se začne pri prvem elementu. To je privzeta nastavitev. -1 – Izvedite obratno iskanje z začetkom pri zadnjem elementu. 2 – Izvedite dvojiško iskanje, ki temelji na lookup_array v naraščajočem vrstnem redu. Če ni razvrščeno, bodo vrnjeni neveljavni rezultati. -2 – Izvedite dvojiško iskanje, ki temelji na lookup_array v padajočem vrstnem redu. Če ni razvrščeno, bodo vrnjeni neveljavni rezultati. |
Primeri
1 . primer uporablja funkcijo XLOOKUP za iskanje imena države v obsegu in vrnitev telefonske kode države. Vključuje argumente lookup_value (celica F2), lookup_array (obseg B2:B11) in argumente return_array (obseg D2:D11). Ne vključuje argumenta match_mode, saj funkcija XLOOKUP privzeto ustvari natančno ujemanje.
Opomba: XLOOKUP uporablja polje za iskanje in vrnjeno matriko, medtem ko VLOOKUP uporablja eno matriko tabele, ki ji sledi številka indeksa stolpca. Enakovredna formula VLOOKUP v tem primeru bi bila: =VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
2. primer poišče podatke o zaposlenih na podlagi številke ID zaposlenega. Za razliko od funkcije VLOOKUP lahko XLOOKUP vrne matriko z več elementi, tako da lahko ena formula vrne tako ime zaposlenega kot tudi oddelek iz celic C5:D14.
———————————————————————————
3 . primer doda if_not_found v prejšnji primer.
———————————————————————————
4. primer v stolpcu C poišče osebne dohodke, vnesene v celico E2, in poišče ujemajočo se davčno stopnjo v stolpcu B. Nastavi, da if_not_found argument vrne 0 (nič), če ni mogoče najti ničesar. Argument match_mode nastavljen na 1, kar pomeni, da bo funkcija poiskala natančno ujemanje. Če ga ne najde, vrne naslednji večji element. Na koncu je search_mode argument nastavljen na 1, kar pomeni, da bo funkcija iskala od prvega elementa do zadnjega.
Opomba: Stolpec funkcije XARRAY lookup_array na desni strani stolpca z return_array, medtem ko lahko funkcija VLOOKUP išče le od leve proti desni.
———————————————————————————
5. primer uporablja ugnezdeno funkcijo XLOOKUP za izvajanje navpičnega in vodoravnega ujemanja. Najprej poišče bruto dobiček v stolpcu B, nato pa poišče »Četa1« v zgornji vrstici tabele (obseg C5:F5), in na koncu vrne vrednost v preseku obeh. To je podobno uporabi funkcij INDEX in MATCH skupaj.
Namig: Funkcijo HLOOKUP lahko zamenjate tudi s funkcijo XLOOKUP .
Opomba: Formula v celicah D3:F3 je: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3;$C 5:$G 5,$C 6:$G 17))).
———————————————————————————
6. primer uporablja funkcijo SUM in dve ugnezdeni funkciji XLOOKUP, da sešteje vse vrednosti med dvema obsegoma. V tem primeru želimo sešteti vrednosti za grozdje, banane in vključiti hruške, ki so med njima.
Formula v celici E3 je: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Kako to deluje? XLOOKUP vrne obseg, tako da pri izračunu formula na koncu izgleda tako: =SUM($E$7:$E$9). Postopek si lahko ogledate sami, tako da izberete celico s formulo XLOOKUP, podobno tej, nato pa izberete Formule > Nadzor formul >Ovrednoti formulo, nato pa izberete Ovrednoti, da izvedete korak med izračunom.
Opomba: Zahvaljujoč MVP-ju za Microsoft Excel, Billu Jelenu, ki je predlagal ta primer.
———————————————————————————
Glejte tudi
Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel ali pa pridobite podporo v skupnostih.
