Funkcija VLOOKUP uvijek je bila jedna od najmoćnijih Excel funkcija. Omogućuje vam traženje vrijednosti u prvom stupcu tablice i vraćanje vrijednosti iz polja s desne strane. Ali Excel također ima funkciju pod nazivom XLOOKUP, koja vam omogućuje traženje vrijednosti u bilo kojem stupcu ili retku i vraćanje podataka iz bilo kojeg drugog stupca.
Kako radi XLOOKUP
Funkciju XLOOKUP mnogo je lakše koristiti od funkcije VLOOKUP, jer umjesto navođenja vrijednosti za stupac rezultata, možete navesti cijeli raspon.
Funkcija vam također omogućuje pretraživanje i stupca i retka, lociranje vrijednosti u ćeliji koja se presijeca.
Parametri funkcije XLOOKUP su sljedeći:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: Vrijednost koju želite pretražiti
- lookup_array: Niz (stupac) koji želite pretraživati
- return_array: Rezultat (stupac) iz kojeg želite dohvatiti vrijednost
- match_mode (izborno): Odaberite točno podudaranje (0), točno podudaranje ili sljedeću najmanju vrijednost (-1) ili podudaranje zamjenskog znaka (2).
- search_mode (opcionalno): Odaberite želite li pretraživati počevši od prve stavke u stupcu (1), zadnje stavke u stupcu (-1), binarnog pretraživanja uzlazno (2) ili binarno pretraživanje silazno (-2).
Slijedi nekoliko najčešćih pretraga koje možete učiniti s funkcijom XLOOKUP.
Kako pretraživati jedan rezultat koristeći XLOOKUP
Najlakši način korištenja XLOOKUP-a je traženje jednog rezultata pomoću podatkovne točke iz jednog stupca.
-
Ovaj primjer proračunske tablice je popis narudžbi koje su predali prodajni predstavnici, uključujući stavku, broj jedinica, cijenu i ukupnu prodaju.
Image -
Ako želite pronaći prvu prodaju na popisu koji je poslao određeni prodajni predstavnik, možete stvoriti funkciju XLOOKUP koja traži ime u stupcu Rep. Funkcija će vratiti rezultat iz stupca Ukupno. Funkcija XLOOKUP za ovo je:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Pokazuje na Rep Name ćeliju za pretraživanje
- C2:C44: Ovo je stupac Rep, koji je polje pretraživanja
- G2:G33: Ovo je stupac Ukupno, koji je povratni niz
- 0: Odabire točno podudaranje
- 1: Odabire prvo podudaranje u rezultatima
-
Kada pritisnete Enter i upišete ime prodajnog predstavnika, ćelija s ukupnim rezultatom prikazat će vam prvi rezultat u tablici za tog prodajnog predstavnika.
Image -
Ako želite pretraživati posljednju rasprodaju (budući da je tablica poredana po datumu obrnutim redoslijedom), promijenite zadnji XLOOKUP argument u - 1, što će započeti pretraživanje iz zadnje ćelije u nizu za traženje i daju vam taj rezultat umjesto toga.
Image -
Ovaj primjer pokazuje slično pretraživanje koje možete izvesti s funkcijom VLOOKUP korištenjem stupca Rep kao prvog stupca tablice pretraživanja. Međutim, XLOOKUP vam omogućuje pretragu bilo kojeg stupca u bilo kojem smjeru. Na primjer, ako želite pronaći prodajnog predstavnika koji je prodao prvu narudžbu Bindera ove godine, upotrijebili biste sljedeću funkciju XLOOKUP:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: Pokazuje na ćeliju za pretraživanje predmeta
- D2:D44: Ovo je stupac stavke, koji je niz za traženje
- C2:C44: Ovo je stupac Rep, koji je povratni niz lijevo od niza pretraživanja
- 0: Odabire točno podudaranje
- 1: Odabire prvo podudaranje u rezultatima
-
Ovaj put, rezultat će biti ime prodajnog predstavnika koji je prodao prvu narudžbu registratora u godini.
Image
Izvršite okomito i vodoravno podudaranje pomoću XLOOKUP-a
Još jedna mogućnost XLOOKUP-a za koju VLOOKUP nije sposoban je mogućnost izvođenja i okomitog i vodoravnog pretraživanja, što znači da možete pretraživati stavku niz stupac, a također i preko retka.
Ova značajka dvostrukog pretraživanja učinkovita je zamjena za druge Excel funkcije kao što su INDEX, MATCH ili HLOOKUP.
-
U sljedećem primjeru proračunske tablice, prodaja za svakog prodajnog predstavnika podijeljena je po četvrtinama. Ako želite vidjeti prodaju u trećem kvartalu za određenog prodajnog predstavnika, bez funkcije XLOOKUP, ova vrsta pretraživanja bi bila teška.
Image -
Uz funkciju XLOOKUP, ova vrsta pretraživanja je jednostavna. Koristeći sljedeću funkciju XLOOKUP, možete tražiti prodaju u trećem kvartalu za određenog prodajnog predstavnika:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: Pokazuje na ćeliju za pretraživanje predstavnika
- B2:B42: Ovo je stupac Artikal, koji je polje traženja stupaca
- K2: Pokazuje na ćeliju za pretraživanje četvrtine
- C1:H1: Ovo je niz za traženje reda
- C2:H42: Ovo je niz za traženje iznosa u dolarima u svakom kvartalu
Ova ugniježđena funkcija XLOOKUP prvo identificira prodajnog predstavnika, a sljedeća funkcija XLOOKUP identificira željeni kvartal. Povratna vrijednost će biti ćelija u kojoj se ta dva presreću.
-
Rezultat za ovu formulu je zarada u prvom kvartalu za predstavnika s imenom Thompson.
Image
Korištenje funkcije XLOOKUP
Funkcija XLOOKUP dostupna je samo pretplatnicima na Office Insider, ali će uskoro biti dostupna svim pretplatnicima na Microsoft 365.
Ako želite sami isprobati funkciju, možete postati Office Insider. Odaberite File > Account, zatim odaberite Office Insider padajući izbornik za pretplatu.
Nakon što se pridružite programu Office Insider, vaša instalirana verzija Excela dobit će sva najnovija ažuriranja i možete početi koristiti funkciju XLOOKUP.
