Pomoću funkcije XLOOKUP pronađite stavke u tablici ili rasponu po retku. Primjerice, potražite cijenu dijela automobila prema broju dijela ili pronađite ime zaposlenika na temelju ID-a zaposlenika. Uz XLOOKUP možete u jednom stupcu potražiti pojam za pretraživanje i vratiti rezultat iz istog retka u drugom stupcu, bez obzira na to na kojoj je strani povratni stupac.
Napomena: XLOOKUP nije dostupan u programima Excel 2016 i Excel 2019. No možete naići na situaciju korištenja radne knjige u programu Excel 2016 ili Excel 2019 s funkcijom XLOOKUP ako je stvorio netko drugi koji koristi noviju verziju programa Excel.
Sintaksa
Funkcija XLOOKUP pretražuje raspon ili polje, a zatim vraća stavku koja odgovara prvom rezultatu koji pronađe. Ako ne postoji podudaranje, XLOOKUP može vratiti najbliže (približno) podudaranje.
=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])
|
Argument |
Opis |
|---|---|
|
vrijednost_pretraživanja Potreban* |
Vrijednost koju treba potražiti *Ako se izostavi, XLOOKUP vraća prazne ćelije koje pronađe u lookup_array. |
|
polje_pretraživanja Obavezno |
Polje ili raspon za pretraživanje |
|
return_array Obavezno |
Polje ili raspon koji će se vratiti |
|
To je if_not_found. Neobavezno |
Ako se ne pronađe valjano podudaranje, vratite [if_not_found] tekst koji upišete. Ako se ne pronađe valjani rezultat i nedostaje [if_not_found], #N/A . |
|
To je match_mode. Neobavezno |
Navedite vrstu podudaranja: 0 – točno podudaranje. Ako nijedan nije pronađen, #N/A. To je zadana mogućnost. -1 - Točno podudaranje. Ako ništa nije pronađeno, vratite sljedeću manju stavku. 1 – točno podudaranje. Ako ništa nije pronađeno, vratite sljedeću veću stavku. 2 – zamjenski znak u kojem *, ?, i ~ imaju posebno značenje. |
|
To je search_mode. Neobavezno |
Odredite način pretraživanja koji će se koristiti: 1 – izvršite pretraživanje počevši od prve stavke. To je zadana mogućnost. -1 – izvođenje obrnutog pretraživanja počevši od zadnje stavke. 2 – Izvođenje binarnog pretraživanja koje se oslanja lookup_array se sortiraju uzlaznim redoslijedom . Ako nije sortirano, prikazat će se rezultati koji nisu valjani. -2 – izvršite binarno pretraživanje koje se oslanja na lookup_array se sortiraju silaznim redoslijedom . Ako nije sortirano, prikazat će se rezultati koji nisu valjani. |
Primjeri
Prvi primjer koristi XLOOKUP da bi potražio naziv države u rasponu, a zatim vratio telefonski pozivni broj države. Obuhvaća argumente lookup_value (ćelija F2), lookup_array (raspon B2:B11) i return_array (raspon D2:D11). Ne sadrži argument match_mode jer XLOOKUP po zadanom daje točno podudaranje.
Napomena: XLOOKUP koristi polje pretraživanja i povratno polje, dok VLOOKUP koristi jedno polje tablice i broj indeksa stupca. Ekvivalentna formula vlookup u ovom slučaju bila bi: =VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
Drugi primjer traži podatke o zaposlenicima na temelju ID broja zaposlenika. Za razliku od funkcije VLOOKUP, XLOOKUP može vratiti polje s više stavki, pa jedna formula može vratiti i ime zaposlenika i odjel iz ćelija C5:D14.
———————————————————————————
Treći primjer dodaje if_not_found argument u prethodni primjer.
———————————————————————————
Četvrti primjer traži osobni prihod unesen u ćeliju E2 u stupcu C i pronalazi odgovarajuću poreznu stopu u stupcu B. Postavlja argument if_not_found vraća 0 (nula ) ako ništa nije pronađeno. Argument match_mode je postavljen na 1, što znači da će funkcija tražiti točno podudaranje, a ako ga ne može pronaći, vraća sljedeću veću stavku. Naposljetku , search_mode je postavljen na 1, što znači da će funkcija pretraživati od prve do zadnje stavke.
Napomena: Stupac XARRAY lookup_array s desne strane stupca return_array , dok VLOOKUP može gledati samo slijeva nadesno.
———————————————————————————
Primjer 5 koristi ugniježđeno XLOOKUP funkciju za izvođenje okomitog i vodoravnog podudaranja. Prvo traži Bruto dobit u stupcu B, a zatim traži kv1 u gornjem retku tablice (raspon C5:F5) i konačno vraća vrijednost na sjecištu te dvije. To je slično korištenju funkcija INDEX i MATCH zajedno.
Savjet: Funkciju HLOOKUP možete zamijeniti i pomoću funkcije XLOOKUP .
Napomena: Formula u ćelijama D3:F3 je: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3;$C 5:$G 5,$C 6:$G 17)).
———————————————————————————
Primjer 6 koristi funkciju SUM i dvije ugniježđene XLOOKUP funkcije za zbrajanje svih vrijednosti između dva raspona. U ovom slučaju, želimo zbrojiti vrijednosti za grožđe, banane, a uključuju kruške, koje su između dva.
Formula u ćeliji E3 je: =SUM(XLOOKUP(B3;B6:B10;E6:E10):XLOOKUP(C3;B6:B10;E6:E10))
Način funkcioniranja XLOOKUP vraća raspon, pa kada izračunava formulu izgleda ovako: =SUM($E$7:$E$9). Možete sami vidjeti kako to funkcionira tako da odaberete ćeliju s formulom XLOOKUP sličnu ovoj, a zatim odaberite Formule > Nadzor formula > Analiza formule, a zatim odaberite Analiza da biste se pomicali po izračunu.
Napomena: Zahvaljujući MVP-u za Microsoft Excel, Billu Jelenu, na prijedlogu ovog primjera.
———————————————————————————
Vidi također
Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.
