Excel XLOOKUP funkcija: sve što trebate znati (10 primjera)

Gledajte video - funkcija Excel XLOOKUP (10 primjera XLOOKUP -a)

Excel XLOOKUP funkcija napokon je stigao.

Ako ste koristili VLOOKUP ili INDEX/MATCH, siguran sam da će vam se svidjeti fleksibilnost koju pruža funkcija XLOOKUP.

U ovom ću vodiču pokriti sve što se može znati o funkciji XLOOKUP i neke primjere koji će vam pomoći da znate kako je najbolje koristiti.

Pa krenimo!

Što je XLOOKUP?

XLOOKUP je nova funkcija Office 365 i nova je i poboljšana verzija funkcije VLOOKUP/HLOOKUP.

Radi sve što je VLOOKUP radio, i mnogo više.

XLOOKUP je funkcija koja vam omogućuje brzo traženje vrijednosti u skupu podataka (okomito ili vodoravno) i vraćanje odgovarajuće vrijednosti u neki drugi redak/stupac.

Na primjer, ako ste dobili bodove za studente na ispitu, možete upotrijebiti XLOOKUP da brzo provjerite koliko je student postigao koristeći ime studenta.

Snaga ove funkcije postat će još jasnija kako duboko zaronim u neke Primjeri XLOOKUP -a kasnije u ovom vodiču.

No prije nego što prijeđem u primjere, postavlja se veliko pitanje - kako mogu pristupiti XLOOKUP -u?

Kako pristupiti XLOOKUP -u?

Do sada je XLOOKUP dostupan samo za korisnike sustava Office 365.

Dakle, ako koristite prethodne verzije programa Excel (2010/2013/2016/2019), nećete moći koristiti ovu funkciju.

Također nisam siguran hoće li ovo ikada biti objavljeno za prethodne verzije ili ne (možda Microsoft može stvoriti dodatak na način na koji su to učinili za Power Query). No od sada ćete ga moći koristiti samo ako ste na usluzi Office 365.

Kliknite ovdje za nadogradnju na Office 365

U slučaju da ste već u sustavu Office 365 (kućno, osobno ili sveučilišno izdanje) i nemate pristup njemu, idite na karticu Datoteka, a zatim kliknite Račun.

Postojao bi program Office Insider i možete kliknuti i pridružiti se programu Office Insider. To će vam omogućiti pristup funkciji XLOOKUP.

Očekujem da će XLOOKUP uskoro biti dostupan na svim verzijama sustava Office 365.

Napomena: XLOOKUP je dostupan i za Office 365 za Mac i Excel za web (Excel na mreži)

Sintaksa funkcije XLOOKUP

Ispod je sintaksa funkcije XLOOKUP:

= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Ako ste koristili VLOOKUP, primijetit ćete da je sintaksa prilično slična, s nekim izvrsnim dodatnim značajkama, naravno.

Ne brinite ako sintaksa i argument izgledaju previše. Pokrivam ih s nekoliko lakih primjera XLOOKUP -a kasnije u ovom vodiču koji će to učiniti kristalno jasnim.

XLOOKUP funkcija može ispričati 6 argumenata (3 obavezna i 3 izborna):

1. lookup_value - vrijednost koju tražite
2. lookup_array - niz u kojem tražite vrijednost pretraživanja
3. return_array - niz iz kojeg želite dohvatiti i vratiti vrijednost (što odgovara položaju na kojem se nalazi tražilica)
4. [ako_nije_nađeno] - vrijednost za povratak u slučaju da se vrijednost pretraživanja ne pronađe. U slučaju da ne navedete ovaj argument, bit će vraćena pogreška #N/A
5. [match_mode] - Ovdje možete odrediti vrstu podudaranja koju želite:
   • 0 - Točno podudaranje, pri čemu bi lookup_value trebala točno odgovarati vrijednosti u lookup_array. Ovo je zadana opcija.
   • -1 - Traži točno podudaranje, ali ako je pronađeno, vraća sljedeću manju stavku/vrijednost
   • 1 - Traži točno podudaranje, ali ako je pronađeno, vraća sljedeću veću stavku/vrijednost
   • 2 - Za djelomično podudaranje pomoću zamjenskih znakova (* ili ~)
6. [način_pretraživanja] - Ovdje određujete kako bi funkcija XLOOKUP trebala pretraživati ​​niz za pretraživanje
   • 1 - Ovo je zadana opcija gdje funkcija počinje tražiti lookup_value od vrha (prva stavka) do dna (zadnja stavka) u nizu za pretraživanje
   • -1 - Radi li pretraživanje odozdo prema gore. Korisno kada želite pronaći posljednju odgovarajuću vrijednost u lookup_array
   • 2 - Obavlja binarno pretraživanje gdje se podaci moraju sortirati uzlaznim redoslijedom. Ako se ne sortira, to može dati pogreške ili pogrešne rezultate
   • -2 - Izvodi binarno pretraživanje gdje se podaci moraju sortirati prema opadajućem redoslijedu. Ako se ne sortira, to može dati pogreške ili pogrešne rezultate

Primjeri funkcija XLOOKUP

Prijeđimo sada na zanimljiv dio - nekoliko praktičnih primjera XLOOKUP -a.

Ovi će vam primjeri pomoći da bolje razumijete kako XLOOKUP radi, kako se razlikuje od VLOOKUP -a i INDEX/MATCH -a i nekih poboljšanja i ograničenja ove funkcije.

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je

Primjer 1: Dohvaćanje vrijednosti traženja

Pretpostavimo da imate sljedeći skup podataka i želite dohvatiti matematički rezultat za Grega (vrijednost pretraživanja).

Ispod je formula koja to čini:

= XLOOKUP (F2, A2: A15, B2: B15)

U gornjoj formuli upravo sam upotrijebio obavezne argumente gdje traži ime (od vrha do dna), pronalazi točno podudaranje i vraća odgovarajuću vrijednost iz B2: B15.

Jedna očita razlika koju imaju XLOOKUP i VLOOKUP funkcija je u načinu na koji postupaju s nizom za pretraživanje. U VLOOKUP-u imate cijeli niz gdje je vrijednost traženja u krajnjem lijevom stupcu, a zatim navodite broj stupca odakle želite dohvatiti rezultat. XLOOKUP, s druge strane, omogućuje vam da odaberete lookup_array i return_array zasebno

Jedna trenutna korist od lookup_array i return_array kao zasebnih argumenata znači da sada možete pogledaj lijevo. VLOOKUP je imao ovo ograničenje gdje možete samo pogledati gore i pronaći vrijednost koja je s desne strane. No s XLOOKUP -om je to ograničenje nestalo.

Evo primjera. Imam isti skup podataka, gdje je ime s desne strane, a return_range s lijeve strane.

Ispod je formula koju mogu koristiti da dobijem rezultat za Grega u matematici (što znači pogledati lijevo od lookup_value)

= XLOOKUP (F2, D2: D15, A2: A15)

XLOOKUP rješava još jedan veliki problem - u slučaju da umetnete novi stupac ili premjestite stupce, rezultirajući podaci i dalje bi bili točni. VLOOKUP bi se vjerojatno slomio ili dao netočan rezultat u takvim slučajevima jer je većina vrijednosti indeksa stupca teško kodirana.

Primjer 2: Potražite i dohvatite cijeli zapis

Uzmimo iste podatke kao primjer.

U ovom slučaju ne želim samo dohvatiti Gregov rezultat u matematici. Želim postići bodove iz svih predmeta.

U ovom slučaju mogu koristiti sljedeću formulu:

= XLOOKUP (F2, A2: A15, B2: D15)

Gornja formula koristi raspon return_array koji je više od stupca (B2: D15). Dakle, kada se vrijednost traženja pronađe u A2: A15, formula vraća cijeli red iz polja return_array.

Također, ne možete izbrisati samo ćelije koje su dio niza koje su automatski popunjene. U ovom primjeru ne možete izbrisati H2 ili I2. Ako pokušate, ništa se ne bi dogodilo. Ako odaberete ove ćelije, formula u traci s formulama bit će zasivljena (što znači da se ne može promijeniti)

Formulu možete izbrisati u ćeliji G2 (gdje smo je izvorno unijeli), izbrisat će cijeli rezultat.

Ovo je korisno poboljšanje kao i ranije s VLOOKUP -om, morat ćete zasebno navesti broj stupca za svaku formulu.

Primjer 3: Dvosmjerno pretraživanje pomoću XLOOKUP -a (vodoravno i okomito pretraživanje)

Ispod je skup podataka u kojem želim znati rezultat Grega iz matematike (subjekt u ćeliji G2).

To se može učiniti pomoću dvosmjernog pretraživanja gdje tražim ime u stupcu A i naziv predmeta u retku 1. Prednost ovog dvosmjernog pretraživanja je u tome što je rezultat neovisan o imenu učenika naziva predmeta. Ako promijenim naziv predmeta u Kemija, ova dvosmjerna XLOOKUP formula i dalje bi radila i dala bi mi točan rezultat.

Ispod je formula koja će izvesti dvosmjerno pretraživanje i dati točan rezultat:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Ova formula koristi ugniježđeni XLOOKUP, gdje je prvo koristim za dohvaćanje svih ocjena učenika u ćeliji F2.

Dakle, rezultat XLOOKUP -a (F2, A2: A15, B2: D15) je {21,94,81}, što je niz ocjena koje je u ovom slučaju postigao Greg.

To se zatim ponovno koristi unutar vanjske formule XLOOKUP kao povratni niz. U vanjskoj formuli XLOOKUP tražim naziv predmeta (koji se nalazi u ćeliji G1), a niz za pretraživanje je B1: D1.

Ako je naziv predmeta Math, ova vanjska XLOOKUP formula dohvaća prvu vrijednost iz polja return - što je {21,94,81} u ovom primjeru.

To čini isto što je do sada postignuto kombinacijom INDEX i MATCH

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je

Primjer 4: Kada vrijednost pretraživanja nije pronađena (rukovanje pogreškama)

U formulu XLOOKUP sada je dodano rukovanje pogreškama.

Četvrti argument u funkciji XLOOKUP je [if_not_found], gdje možete navesti što želite u slučaju da se pretraživanje ne može pronaći.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku gdje želite dobiti rezultat iz matematike u slučaju da se podudara, a u slučaju da naziv nije pronađen, želite se vratiti - "Nije se pojavio"

Formula u nastavku će to učiniti:

= XLOOKUP (F2, A2: A15, B2: B15, "Nije se pojavio")

U ovom sam slučaju teško kodirao ono što želim dobiti u slučaju da nema podudaranja. Također možete upotrijebiti referencu ćelije na ćeliju ili formulu.

Primjer 5: Umetnuti XLOOKUP (pretraživanje u više raspona)

Genijalnost argumenta [if_not_found] je u tome što vam omogućuje korištenje ugniježđena XLOOKUP formula.

Na primjer, pretpostavimo da imate dva odvojena popisa kao što je prikazano u nastavku. Dok ove dvije tablice imam na istom listu, možete ih staviti u zasebne listove ili čak radne bilježnice.

Dolje je ugniježđena formula XLOOKUP koja će provjeriti ime u obje tablice i vratiti odgovarajuću vrijednost iz navedenog stupca.

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

U gornjoj formuli sam koristio argument [if_not_found] za upotrebu druge formule XLOOKUP. To vam omogućuje dodavanje drugog XLOOKUP -a u istu formulu i skeniranje dvije tablice s jednom formulom.

Nisam siguran koliko ugniježđenih XLOOKUP -ova možete koristiti u formuli. Pokušao sam do 10 i uspjelo je, a onda sam odustao 🙂

Primjer 6: Pronađite zadnju odgovarajuću vrijednost

Ovaj je bio prijeko potreban i XLOOKUP je to omogućio. Sada ne morate pronaći zamršene načine da biste dobili zadnju odgovarajuću vrijednost u rasponu.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite provjeriti kada je zadnja osoba zaposlena u svakom odjelu i koji je datum bio zaposlen.

Formula u nastavku će potražiti posljednju vrijednost za svaki odjel i dati naziv zadnjeg zaposlenika:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)

I donja formula će dati datum zadnjeg zapošljavanja za svaki odjel:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)

Budući da XLOOKUP ima ugrađenu značajku za određivanje smjera pretraživanja (od prvog do zadnjeg ili od posljednjeg do prvog), to se radi pomoću jednostavne formule. S vertikalnim podacima, VLOOKUP i INDEX/MATCH uvijek gledaju odozgo prema dolje, ali s XLOOKUP -om i mogu odrediti smjer i odozdo prema gore.

Primjer 7: Približno podudaranje s XLOOKUP -om (Pronađi stopu poreza)

Još jedno značajno poboljšanje s XLOOKUP -om je to što sada postoje četiri načina podudaranja (VLOOKUP ima 2, a MATCH 3).

Možete odrediti bilo koji od četiri argumenta kako biste odlučili kako se vrijednost pretraživanja mora uskladiti:

• 0 - Točno podudaranje, pri čemu bi lookup_value trebala točno odgovarati vrijednosti u lookup_array. Ovo je zadana opcija.
• -1 - Traži točno podudaranje, ali ako je pronađeno, vraća sljedeću manju stavku/vrijednost
• 1 - Traži točno podudaranje, ali ako je pronađeno, vraća sljedeću veću stavku/vrijednost
• 2 - Za djelomično podudaranje pomoću zamjenskih znakova (* ili ~)

Ali najbolji dio je što ne morate brinuti jesu li vaši podaci razvrstani uzlaznim ili silaznim redoslijedom. Čak i ako podaci nisu sortirani, XLOOKUP će se pobrinuti za to.

Ispod imam skup podataka u kojem želim pronaći proviziju svake osobe - a proviziju je potrebno izračunati pomoću tablice s desne strane.

Ispod je formula koja će to učiniti:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2

Ovo jednostavno koristi vrijednost prodaje za pretraživanje i pregledava tablicu za pretraživanje s desne strane. U ovoj formuli sam koristio -1 kao peti argument ([match_mode]), što znači da će tražiti točno podudaranje, a kad ga ne pronađe, vratit će vrijednost samo manju od tražene vrijednosti .

I kao što sam rekao, ne morate brinuti jesu li vaši podaci poredani.

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je

Primjer 8: Horizontalno traženje

XLOOKUP može raditi okomito kao i vodoravno pretraživanje.

Ispod imam skup podataka u kojem imam imena učenika i njihove rezultate u redovima, i želim dohvatiti rezultat za ime u ćeliji B7.

Formula u nastavku će to učiniti:

= XLOOKUP (B7, B1: O1, B2: O2)

Ovo nije ništa drugo do jednostavno pretraživanje (slično onome što smo vidjeli u primjeru 1), već vodoravno.

Svi primjeri koje navodim o vertikalnom traženju mogu se izvesti i vodoravnim pretraživanjem pomoću XLOOKUP -a (oproštaj od VLOOKUP -a i HLOOKUP -a).

Primjer 9: Uvjetno pretraživanje (Korištenje XLOOKUP -a s drugim formulama)

Ovo je malo napredniji primjer, a također pokazuje snagu XLOOKUP -a kada trebate napraviti složene pretrage.

Ispod je skup podataka u kojem imam imena učenika i njihove ocjene te želim znati ime učenika koji je postigao maksimum iz svakog predmeta i broj učenika koji je postigao više od 80 bodova u svakom predmetu.

Ispod je formula koja će dati ime učenika s najvećim ocjenama u svakom predmetu:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)

Budući da se XLOOKUP može koristiti za vraćanje cijelog niza, iskoristio sam ga za prvo dobivanje svih ocjena za traženi predmet.

Na primjer, za matematiku, kada koristim XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), to mi daje sve ocjene iz matematike. Zatim mogu koristiti funkciju MAX za pronalaženje maksimalnog rezultata u ovom rasponu.

Taj maksimalni rezultat tada postaje moja vrijednost pretraživanja, a raspon pretraživanja bio bi niz koji vraća XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Koristim ovo unutar druge XLOOKUP formule za dohvaćanje imena učenika koji je postigao maksimalne ocjene.

A da biste izbrojali broj učenika koji su postigli više od 80 bodova, upotrijebite donju formulu:

= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

Ovaj jednostavno koristi formulu XLOOKUP za dobivanje raspona svih vrijednosti za dati predmet. Zatim ga umotava u funkciju COUNTIF kako bi dobio broj bodova koji su veći od 80.

Primjer 10: Korištenje zamjenskih znakova u XLOOKUP -u

Baš kao što možete koristiti zamjenske znakove u VLOOKUP -u i MATCH -u, to možete učiniti i s XLOOKUP -om.

Ali postoji razlika.

U XLOOKUP -u morate navesti da koristite zamjenske znakove (u petom argumentu). Ako to ne navedete, XLOOKUP će vam dati pogrešku.

Ispod je skup podataka u kojem imam nazive tvrtki i njihovu tržišnu kapitalizaciju.

Želim potražiti naziv tvrtke u stupcu D i dohvatiti tržišnu kapitalizaciju iz tablice s lijeve strane. A budući da se imena u stupcu D ne podudaraju točno, morat ću koristiti zamjenske znakove.

Ispod je formula koja će to učiniti:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

U gornjoj formuli ranije sam koristio zamjensku zvjezdicu (*) kao i nakon D2 (mora biti unutar dvostrukih navodnika i spojiti s D2 pomoću znaka &).

To govori formuli da pregleda sve ćelije, a ako sadrži riječ u ćeliji D2 (koja je Apple), smatrajte je točnom podudarnošću. Bez obzira koliko i koji znakovi su prije i poslije teksta u ćeliji D2.

A kako bi XLOOKUP prihvatio zamjenske znakove, peti argument postavljen je na 2 (podudaranje zamjenskih znakova).

Primjer 11: Pronađite posljednju vrijednost u stupcu

Budući da XLOOKUP omogućuje pretraživanje odozdo prema gore, možete lako pronaći zadnju vrijednost na popisu, kao i dohvatiti odgovarajuću vrijednost iz stupca.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite znati koja je posljednja tvrtka i kolika je tržišna kapitalizacija ove zadnje tvrtke.

Formula ispod daje vam ime posljednje tvrtke:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

I donja formula će dati tržišnu kapitalizaciju posljednje tvrtke na popisu:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

Ove formule opet koriste zamjenske znakove. U njima sam upotrijebio zvjezdicu (*) kao vrijednost pretraživanja, što znači da bi se prva ćelija na koju naiđe smatra točnim podudaranjem (jer zvjezdica može biti bilo koji znak i bilo koji broj znakova).

A budući da je smjer odozdo prema gore (za vertikalno poredane podatke), vratit će zadnju vrijednost na popisu.

I druga formula budući da koristi zasebni return_range za dobivanje tržišne kapitalizacije prezimena na popisu.

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je

Što ako nemate XLOOKUP?

Budući da će XLOOKUP vjerojatno biti dostupan samo korisnicima sustava Office 365, jedan od načina za to je nadogradnja na Office 365.

Ako već imate Office 365 kućno, osobno ili sveučilišno izdanje, već imate pristup XLOOKUP -u. Sve što trebate učiniti je pridružiti se programu Office Insider.

Da biste to učinili, idite na karticu Datoteka, kliknite na Račun, a zatim kliknite na opciju Insider Office. Postojala bi mogućnost pridruživanja insajderskom programu.

U slučaju da imate druge pretplate na Office 365 (poput Enterprisea), siguran sam da će XLOOKUP i druge izvrsne značajke (poput dinamičkih nizova, formula poput SORT i FILTER) uskoro postati dostupne.

U slučaju da koristite Excel 2010/2013/2016/2019, nećete imati XLOOKUP, pa ćete morati nastaviti koristiti kombinaciju VLOOKUP, HLOOKUP i INDEX/MATCH da biste najbolje iskoristili formule za pretraživanje.

XLOOKUP Kompatibilnost unatrag

Ovo je jedna stvar oko koje morate biti oprezni - XLOOKUP je NISU kompatibilni unatrag.

To znači da ako stvorite datoteku i koristite formulu XLOOKUP, a zatim je otvorite u verziji koja nema XLOOKUP, prikazat će se pogreške.

Budući da je XLOOKUP veliki korak naprijed u pravom smjeru, vjerujem da će to postati zadana formula za pretraživanje, ali će sigurno proći nekoliko godina prije nego što se široko usvoji. Uostalom, još uvijek vidim neke ljude koji koriste Excel 2003.

Dakle, ovo je 11 primjera XLOOKUP -a koji vam mogu pomoći da brže izvršite sve pretrage i reference i učinite ih lakim za korištenje.

Nadam se da vam je ovaj vodič bio koristan!