Funkcija VLOOKUP oduvijek je bila jedna od najmoćnijih Excelovih funkcija. Omogućava vam da tražite vrijednosti u prvoj koloni tabele i vratite vrijednosti iz polja na desnoj strani. Ali Excel također ima funkciju koja se zove XLOOKUP, koja vam omogućava da tražite vrijednost u bilo kojoj koloni ili redu i vratite podatke iz bilo koje druge kolone.
Kako radi XLOOKUP
Funkcija XLOOKUP je mnogo lakša za korištenje od funkcije VLOOKUP, jer umjesto specificiranja vrijednosti za kolonu rezultata, možete specificirati cijeli raspon.
Funkcija vam takođe omogućava da pretražujete i kolonu i red, locirajući vrijednost 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 tražiti
- lookup_array: Niz (kolona) koji želite da pretražite
- return_array: Rezultat (kolona) iz kojeg želite da preuzmete vrijednost
- match_mode (opciono): Odaberite tačno podudaranje (0), tačno podudaranje ili sljedeću najmanju vrijednost (-1) ili podudaranje sa zamjenskim znakom (2).
- režim_pretraživanja (opciono): Odaberite hoćete li pretraživati počevši od prve stavke u koloni (1), posljednje stavke u koloni (-1), binarno pretraživanje uzlazno (2) ili binarno pretraživanje silazno (-2).
Slijedi nekoliko najčešćih pretraživanja koje možete učiniti s funkcijom XLOOKUP.
Kako tražiti pojedinačni rezultat koristeći XLOOKUP
Najlakši način da koristite XLOOKUP je da tražite jedan rezultat koristeći tačku podataka iz jedne kolone.
-
Ovaj primjer tabele je lista narudžbi koje su predali predstavnici prodaje, uključujući artikl, broj jedinica, trošak i ukupnu prodaju.
Image -
Ako želite da pronađete prvu prodaju na listi koju je dostavio određeni prodajni predstavnik, možete kreirati funkciju XLOOKUP koja traži ime u koloni Rep. Funkcija će vratiti rezultat iz kolone Total. Funkcija XLOOKUP za ovo je:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Pokazuje na Rep Name ćeliju za pretragu
- C2:C44: Ovo je kolona Rep, koja je niz za traženje
- G2:G33: Ovo je kolona Total, koja je povratni niz
- 0: Odabire tačno podudaranje
- 1: Odabire prvu utakmicu u rezultatima
-
Kada pritisnete Enter i upišete ime prodajnog predstavnika, ćelija Ukupni rezultat će vam pokazati prvi rezultat u tabeli za tog prodajnog predstavnika.
Image -
Ako želite tražiti najnoviju prodaju (pošto je tabela poredana po datumu obrnutim redoslijedom), promijenite zadnji argument XLOOKUP u - 1, koji će početi pretraživanje od posljednje ćelije u nizu pretraživanja i umjesto toga vam dati taj rezultat.
Image -
Ovaj primjer pokazuje sličnu pretragu koju biste mogli izvršiti sa funkcijom VLOOKUP koristeći kolonu Rep kao prvu kolonu tabele pretraživanja. Međutim, XLOOKUP vam omogućava da tražite bilo koju kolonu u oba smjera. Na primjer, ako želite pronaći prodajnog predstavnika koji je prodao prvu narudžbu Bindera u godini, koristili biste sljedeću funkciju XLOOKUP:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: Pokazuje na ćeliju za pretragu predmeta
- D2:D44: Ovo je kolona stavke, koja je niz za traženje
- C2:C44: Ovo je kolona Rep, koja je povratni niz lijevo od niza pretraživanja
- 0: Odabire tačno podudaranje
- 1: Odabire prvu utakmicu u rezultatima
-
Ovaj put, rezultat će biti ime prodajnog predstavnika koji je prodao prvu narudžbu registratora u godini.
Image
Izvršite vertikalno i horizontalno podudaranje sa XLOOKUP
Još jedna mogućnost XLOOKUP-a za koju VLOOKUP nije sposoban je mogućnost obavljanja i vertikalne i horizontalne pretrage, što znači da možete tražiti stavku niz kolonu, kao i preko reda.
Ova funkcija dvostrukog pretraživanja je efikasna zamjena za druge Excel funkcije kao što su INDEX, MATCH ili HLOOKUP.
-
U sljedećem primjeru tabele, prodaja za svakog prodajnog predstavnika je podijeljena po kvartalu. Ako želite da vidite prodaju u trećem kvartalu za određenog predstavnika prodaje, bez funkcije XLOOKUP, ovakva pretraga bi bila teška.
Image -
Sa funkcijom XLOOKUP, ova vrsta pretrage je laka. 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 pretragu Rep
- B2:B42: Ovo je kolona stavke, koja je niz za traženje stupaca
- K2: Pokazuje na ćeliju za pretragu četvrtina
- C1:H1: Ovo je niz za traženje reda
- C2:H42: Ovo je niz pretraživanja za iznos u dolarima u svakom kvartalu
Ova ugniježđena XLOOKUP funkcija prvo identificira prodajnog predstavnika, a sljedeća funkcija XLOOKUP identificira željeni kvartal. Povratna vrijednost je ćelija u kojoj se ta dva presjeću.
-
Rezultat za ovu formulu je četvrtina zarade predstavnika sa imenom Thompson.
Image
Upotreba funkcije XLOOKUP
Funkcija XLOOKUP dostupna je samo pretplatnicima Office Insider-a, ali će uskoro biti predstavljena svim Microsoft 365 pretplatnicima.
Ako želite sami testirati funkciju, možete postati Office Insider. Odaberite File > Account, a zatim odaberite Office Insider padajući meni da se pretplatite.
Kada se pridružite programu Office Insider, vaša instalirana verzija Excel-a će dobiti sva najnovija ažuriranja i možete početi koristiti funkciju XLOOKUP.
