Kako koristiti funkciju VLOOKUP u Excelu

Želite li znati kako koristiti Funkcija VLOOKUP u Excelu? U ovom vodiču pokazat ćemo vam kako koristiti funkciju VLOOKUP za kopiranje podataka s drugog radnog lista ili radne knjige, Vlookup na više listova i dinamičko pretraživanje za vraćanje vrijednosti s različitih listova u različite ćelije.

Prilikom traženja informacija u Excelu rijetko se događa da su svi podaci na istom listu. Češće ćete morati pretraživati ​​više listova ili čak različitih radnih knjiga. Dobra vijest je da Microsoft Excel nudi više od jednog načina za to, a loša vijest je da su svi obrasci malo kompliciraniji od standardne VLOOKUP formule. Ali uz malo strpljenja, naučit ćete.

Kako koristiti funkciju VLOOKUP u Excelu između dva lista

Za početak ćemo staviti jednostavan slučaj: upotrijebite funkciju VLOOKUP u Excelu za kopiranje podataka s drugog radnog lista. Vrlo je slična normalnoj VLOOKUP formuli koja pretražuje isti radni list. Razlika je u tome što uključuje naziv lista u argumentu table_array da svojoj formuli kažete na kojem se radnom listu nalazi raspon pretraživanja.

Ovdje možete naučiti o: Kako poboljšati svoje iskustvo u Excelu – 13 korisnih savjeta

Generička formula za Funkcija VLOOKUP u Excelu s drugog lista je sljedeće:

• VLOOKUP (traži_vrijednost, List! Raspon, col_index_num, [range_lookup])

Kao primjer, izdvojimo podatke o prodaji iz izvješća za siječanj na listu Sažetak. Da biste to učinili, morate definirati sljedeće argumente:

• Traži vrijednosti Nalaze se u stupcu A lista Rezime i upućujemo na prvu podatkovnu ćeliju, koja je A2.
• Niz_tabliceje rang A2: B6 na siječanjskom listu. Da biste ga referencirali, morate ispred reference raspona staviti naziv lista iza kojeg slijedi uskličnik: Jan! $A$2:$B$6.

NAPOMENA: Ovdje morate obratiti pozornost da zaključate raspon s apsolutnim referencama ćelije kako biste spriječili njegovu promjenu kada kopirate formulu u druge ćelije.

• Col_index_numje 2 jer želite kopirati vrijednost iz stupca B, koji je drugi stupac u matrici tablice.
• Traženje_rasponaje postavljeno na FALSE za pronalaženje točnog podudaranja.

Spajajući argumente zajedno, dobivate ovu formulu:

• =VLOOKUP(A2, Jan!$A$2:$B$6, 2, FALSE)

Povucite formulu niz stupac i dobit ćete ovaj rezultat:

Slično, možete napraviti Vlookup funkciju koristeći podatke iz listova za veljaču i ožujak:

• =VLOOKUP(A2, veljača!$A$2:$B$6, 2, FALSE)
• =VLOOKUP(A2, uto!$A$2:$B$6, 2, FALSE)

Savjeti i bilješke o funkciji VLOOKUP u programu Excel:

• Ako naziv lista sadrži prostoriili likovi neabecedni, moraju biti u jednostrukim navodnicima, kao što je 'Siječanjske akcije'! $A$2:$B$6.
• Umjesto da upisujete naziv lista izravno u formulu, možete se prebaciti na radni list za pretraživanje i tamo odabrati raspon. Excel će automatski umetnuti referencu s ispravnom sintaksom, uštedjevši vam probleme s provjerom naziva i rješavanjem problema.

Metode za korištenje funkcije VLOOKUP u Excelu

Uđimo sada u detalje korištenja funkcije VLOOKUP u Excelu. Ovdje vam ostavljamo neke opcije:

Metoda 1: Vpretraga druge radne knjige

Da biste koristili funkciju VLOOKUP u Excelu između dvije radne knjige, morate staviti naziv datoteke u uglate zagrade, nakon čega slijedi naziv lista i uskličnik.

• Na primjer: pronaći vrijednost A2 u rasponu A2:B6 na siječanjskom listu u radnoj bilježnici xlsx, koristite ovu formulu:
• =VLOOKUP(A2, [Sales_reports.xlsx]Siječanj!$A$2:$B$6, 2, FALSE)

Metoda 2: Vlookup na više listova s ​​IFERROR

Kada trebate pretraživati ​​između više od dva lista, najjednostavnije rješenje je korištenje funkcije VLOOKUP u Excelu u kombinaciji s IFERROR. Ideja je ugniježditi nekoliko funkcija IFERROR za provjeru više radnih listova jedan po jedan: ako prvi VLOOKUP ne pronađe podudaranje na prvom listu, traži se na sljedećem listu, i tako dalje. Na primjer:

• POGREŠKA (VLOOKUP (…), IFERROR (VLOOKUP (…),…, » Nije pronađeno«))

Da bismo vidjeli kako ovaj pristup funkcionira na podacima iz stvarnog života, razmotrimo sljedeći primjer:

Ispod je tablica od resumen koje morate ispuniti nazivima artikala i količinama kada tražite broj narudžbe na zapadnim i istočnim listovima:

• korak 1: Prvo ćete izvaditi elemente. Da bismo to učinili, naznačimo formuli VLOOKUP Pronađite broj narudžbe u formatu A2 na listu ovo i vrati vrijednost stupca B (2. stupac u niz_tablice A2:C6).
• korak 2: Ako nije pronađeno točno podudaranje, pretražite list zapad.
• korak 3: Da oboje Pad Vlookupa, vratit će poruku: "Nije pronađeno".
  • =IFERROR(VLOOKUP(A2, istok!$A$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(A2, zapad!$A$2:$C$6, 2, FALSE), «Nije pronađeno»))

• korak 4: Da biste vratili iznos, jednostavno promijenite broj indeksa stupca u 3:
  • =IFERROR(VLOOKUP(A2, istok!$A$2:$C$6, 3, FALSE), IFERROR(VLOOKUP(A2, zapad!$A$2:$C$6, 3, FALSE), «Nije pronađeno»))

Vijeće: Ako je potrebno, možete navesti različite tablične nizove za različite VLOOKUP funkcije. U ovom primjeru oba lista za pretraživanje imaju isti broj redaka (A2:C6), ali vaši radni listovi mogu biti različite veličine.

Metoda 3: Vlookup na više radnih knjiga

Za pretraživanje između dvije ili više radnih knjiga, naziv radne knjige morate staviti u uglate zagrade i staviti ga ispred naziva lista.

• Na primjer- Ovako možete koristiti Vlookupdvije različite datoteke (Knjiga1 i Knjiga2) s jednom formulom:
  • =IFERROR(VLOOKUP(A2, [Book1.xlsx]Istok!$A$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(A2, [Book2.xlsx]Zapad!$A$2:$C$6, 2, FALSE),»Nije pronađeno»))

Metoda 4: Učinite broj indeksa stupca dinamičnim za Vlookup više stupaca

U situaciji kada trebate vratiti podatke iz više stupaca, imajte col_index_num Budi dinamičan mogao bi ti uštedjeti vrijeme. Morate izvršiti nekoliko prilagodbi:

• Za argument col_index_num, koristite funkciju KOLONE koji vraća broj stupaca u određenom nizu: STUPCI ($A$1:B$1). (Koordinata retka zapravo nije bitna, može biti bilo koji red.)
• U argumentu lookup_value, zaključava referencu stupca s znak $ ($A2), tako da ostaje fiksirana prilikom kopiranja formule u druge stupce.

Kao rezultat toga, dobit ćete neku vrstu dinamičke formule koja izvlači podudarne vrijednosti iz različitih stupaca, ovisno o tome u koji se stupac formula kopira:

• =IFERROR(VLOOKUP($A2, istok!$A$2:$C$6, COLUMNS($A$1:B$1), FALSE), IFERROR(VLOOKUP($A2, zapad!$A$2:$C$6, COLUMNS( $A$1:B$1), FALSE), «Nije pronađeno»)

Kada se unese u stupac B, STUPCI ($A$1:B$1) daje vrijednost 2 i govori VLOOKUP-u da vrati vrijednost 2 drugi stupac u matrici tablice.

Kada kopirate u stupac C (odnosno, povukli ste formulu iz B2 u C2), B$1 se mijenja u C$1 jer je referenca stupca relativna. Posljedično, STUPCI ($A$1:C$1) daje vrijednost 3, što prisiljava funkciju VLOOKUP u Excelu da vrati vrijednost iz trećeg stupca.

Ova formula izvrsno funkcionira za 2 ili 3 lista za pretraživanje. Ako imate više, ponavljajuće IFERROR-ove postaju preglomazne. Sljedeći primjer pokazuje malo kompliciraniji, ali mnogo elegantniji pristup.

Metoda 5: Vlookup više listova s ​​INDIRECT

Još jedan način VLOOKUP-a između više listova u Excelu je korištenje kombinacije funkcija VLOOKUP i INDIRECT. Ova metoda zahtijeva malo pripreme, ali na kraju ćete imati kompaktniju formulu za Vlookup u bilo kojem broju proračunskih tablica.

Generička formula za Vlookup na listovima je sljedeća:

• VLOOKUP ( tražena_vrijednost, INDIREKTNO («'» & INDEKS ( Lookup_sheets , MATCH (1, – (COUNTIF (INDIRECT («'» & Popis_listova & «'! Raspon_traženja "), tražena_vrijednost )> 0), 0)) & «'! table_array "), col_index_num , NETOČNO)

gdje je:

• Lookup_sheets- Imenovani raspon koji se sastoji od naziva lista za pretraživanje.
• Lookup_value: vrijednost za traženje.
• Raspon_traženja- Raspon stupaca u preglednim listovima u kojima se traži tražena vrijednost.
• Niz tablica- Raspon podataka u preglednim listovima.
• Col_index_num- Broj stupca u polju tablice iz kojeg se vraća vrijednost.

Kako bi formula ispravno radila, morate uzeti u obzir sljedeća upozorenja:

1. Ovo je formula polja koja se mora dovršiti pritiskom na Ctrl + Shift + Enter ključeve zajedno.
2. Svi listovi moraju imati isti redoslijed stupaca.
3. Budući da koristite polje tablice za sve listove pretraživanja, navedite najveći raspon ako vaši listovi imaju različit broj redaka.

Kako koristiti formulu za Vlookup na listovima

Za korištenje značajke Vlookup na više listova odjednom, morate slijediti ove korake:

• korak 1: Zapišite sve nazive lista za pretraživanje negdje u svoju radnu bilježnicu i dajte naziv tom rasponu (Lookup_sheets u ovom slučaju).

• korak 2: Prilagodite generičku formulu za svoje podatke. U ovom primjeru to bi bilo:
  • tražimo vrijednost A2 (tražena_vrijednost)
  • u rasponu A2: A6 (raspon_traženja) na četiri radna lista (Istok, sjever, jug i zapad), i
  • izvucite podudarne vrijednosti iz stupca B, koji je stupac 2 (sol_index_num) u rasponu podataka A2: C6 (table_array).

Uz prethodne argumente, formula ima ovaj oblik:

• =VLOOKUP ($A2, INDIRECT («'»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («'»& Lookup_sheets&»'!$A$2:$A$6″), $A2)>0), 0)) &»'!$A$2:$C$6»), 2, FALSE)

NAPOMENA: Imajte na umu da blokiramo oba raspona ($A$2:$A$6 i $A$2:$C$6) s apsolutnim referencama ćelije.

• korak 3: Unesite formulu u gornju ćeliju (B2 u ovom primjeru) i pritisnite Ctrl + Shift + Enter da ga dovršim.
• korak 4: Dvaput kliknite ili povucite ručicu za popunjavanje da kopirate formulu niz stupac.

Kao rezultat, imat ćete formulu za traženje broja narudžbe na 4 lista i dohvaćanje odgovarajuće stavke. Ako određeni broj narudžbe nije pronađen, prikazuje se pogreška #N/A kao u redu 14:

Da biste vratili iznos, jednostavno zamijenite 2 s 3 u argumentu col_index_num budući da su količine u 3. stupcu matrice tablice:

• =VLOOKUP($A2, INDIRECT(«'»&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(«'» & Lookup_sheets & «'!$A$2:$A$6»), $A2)>0) , 0)) & «'!$A$2:$C$6»), 3, FALSE)

Ako želite zamijeniti standardni zapis pogreške #N/A s vlastitim tekstom zamotajte formulu u funkciju IFNA:

• =IFNA (VLOOKUP($A2, INDIRECT («'»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF(INDIRECT («'» & Lookup_sheets & «'!$A$2:$A$6»), $A2)> 0), 0)) & «'!$A$2:$C$6»), 3, FALSE), «Nije pronađeno»)

Vlookup više listova između radnih knjiga

Ova generička formula (ili bilo koja varijacija) također se može koristiti za prikaz više listova u jednom različita radna bilježnica. Da biste to učinili, spojite naziv radne knjige unutar INDIRECT kao što je prikazano u sljedećoj formuli:

• =IFNA(VLOOKUP ($A2, INDIRECT («'[Book1.xlsx]» & INDEX (Lookup_sheets, MATCH (1, –(COUNTIF(INDIRECT («'[Book1.xlsx]» & Lookup_sheets & «'!$A$2) :$A$6»), $A2)>0), 0)) & «'!$A$2:$C$6»), 2, FALSE), «Nije pronađeno»)

Vlookup između listova i vraća više stupaca

Ako želite izdvojiti podatke iz više stupaca, jedan formula niza s više ćelija možete to učiniti u jednom potezu. Da biste stvorili takvu formulu, navedite konstantu polja za argument col_index_num.

U ovom primjeru vraćamo nazive elemenata (stupac B) i količine (stupac C), koji su 2. odnosno 3. stupac u matrici tablice. Dakle, tražena matrica je 2,3 {}.

• =VLOOKUP($A2, INDIRECT(«'»&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(«'»& Lookup_sheets &»'!$A$2:$C$6″), $A2)>0) , 0)) &»'!$A$2:$C$6»), {2,3}, FALSE)

Da biste uspješno unijeli formulu u više ćelija, morate učiniti sljedeće:

• korak 1: U prvom retku odaberite sve ćelije koje treba ispuniti (B2:C2 u ovom primjeru).
• korak 2: Napišite formulu i pritisnite tipke Ctrl + Shift + Enter. Ovo unosi istu formulu u odabrane ćelije, koja će vratiti različitu vrijednost u svakom stupcu.
• korak 3: Povucite formulu u preostale retke.

Kako funkcionira formula funkcije VLOOKUP u Excelu

Kako bismo bolje razumjeli logiku, raščlanimo ovu osnovnu formulu na pojedinačne funkcije:

• =VLOOKUP ($A2, INDIRECT («'»&INDEX(Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («'»& Lookup_sheets&»'!$A$2:$A$6″), $A2)>0), 0)) &»'!$A$2:$C$6»), 2, FALSE)

Radeći iznutra prema van, ovo je ono što formula čini:

COUNTIF i INDIRECT

Ukratko, jaNIZRAVNO stvara reference za sve listove pretraživanja, a COUNTIF broji pojavljivanja vrijednosti pretraživanja (A2) na svakom listu:

• –(COUNTIF( INDIRECT(«'»&Lookup_sheets&»'!$A$2:$A$6»), $A2)>0)

Detaljnije:

Prvo povežite naziv raspona (Traženje_tablica) i referenca raspona ($A$2:$A$6), dodavanjem apostrofa i uskličnika na pravim mjestima kako bi se napravila vanjska referenca i unos rezultirajućeg tekstualnog niza u funkciju INDIRECT za dinamičko referenciranje listova za pretraživanje:

• INDIREKTNO ({«'Istok'!$A$2:$A$6»; «'Jug'!$A$2:$A$6»; «'Sjever'!$A$2:$A$6»; «'Zapad'! $A$2:$A$6»})

RAČUNATI.SI provjerava svaku ćeliju u rasponu A2: A6 na svakom listu za pretraživanje s vrijednošću u A2 na nadređenom listu i vraća broj podudaranja za svaki list.

U ovom skupu podataka, broj naloga u A2 (101) je u list zapad, što je 4 ^º u imenovanom rasponu, dakle TAČKA AKO vraća ovaj niz:

• {0; 0; 0; 1}

Zatim usporedite svaki element gornjeg niza s 0:

• –({0; 0; 0; 1}>0)

Ovo proizvodi niz vrijednosti TRUE (veće od 0) i FALSE (jednako 0), koji prisiljava 1 i 0 korištenjem unarnog dvostruko (-), i kao rezultat dobivate sljedeći niz:

• {0; 0; 0; 1}

Ova je operacija dodatna mjera opreza za rješavanje situacije u kojoj lista za pretraživanje sadrži više pojavljivanja vrijednosti za pretraživanje, u kojoj COUNTIF slučaj vratilo bi broj veći od 1, dok vi samo želite 1 i 0 u konačnoj matrici (uskoro ćete shvatiti zašto).

Nakon svih ovih transformacija, formula izgleda ovako:

• VLOOKUP($A2, INDIRECT(«'»&INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0)) &»'!$A$2:$C$6»), 2, FALSE)

INDEKS i ŠIBICA

U ovom trenutku, klasična kombinacija INDEX MATCH ide na:

• INDEX(Traženje_tablica, MATCH(1, {0;0;0;1}, 0))

Funkcija COINCIDE postavljen na točno podudaranje (0 u zadnjem argumentu) traži vrijednost 1 u nizu {0; 0; 0; 1} i vraća njegovu poziciju, koja je 4:

INDEX(Traženje_listova, 4)

Funkcija INDEX upotrijebite broj koji je vratio COINCIDE kao argument broja retka (broj_reda) i vraća četvrtu vrijednost u imenovanom rasponu Lookup_sheets, Što je zapad.

Dakle, formula se dalje svodi na:

• VLOOKUP($A2, INDIRECT(«'»&»Zapad»&»'!$A$2:$C$6»), 2, FALSE)

VLOOKUP i INDIRECT

Funkcija INDIREKTNO obraditi tekstualni niz unutar njega:

• NEIZRAVNO («'»&»Zapad»&»'!$A$2:$C$6»)

I pretvara to u referencu koja ide na argument table_array iz VLOOKUP-a:

• VLOOKUP ($A2, 'Zapad'!$A$2:$C$6, 2, FALSE)

Konačno, ova vrlo standardna VLOOKUP formula traži vrijednost A2 u prvom stupcu raspona A2:C6 na zapadnom listu i vraća podudaranje iz drugog stupca.

Dinamički VLOOKUP za vraćanje podataka s više listova u različite ćelije

Prije svega, definirajmo što ta riječ točno znači "dinamičan" u tom kontekstu i po čemu će se ova formula razlikovati od prethodnih.

U slučaju da imate velike količine podataka u istom formatu koji su podijeljeni na više proračunskih tablica, možda ćete htjeti izdvojiti informacije s različitih listova u različite ćelije. Sljedeća slika ilustrira koncept:

Za razliku od prethodnih formula koje su dohvaćale vrijednost s određenog lista na temelju jedinstvenog identifikatora, Ovaj put ćete pokušati izvući vrijednosti iz nekoliko listova u isto vrijeme.

Za ovaj zadatak postoje dva različita rješenja. U oba slučaja, trebate napraviti malu pripremu i stvoriti imenovane raspone za podatkovne ćelije na svakom listu za pretraživanje. Za ovaj primjer definiramo sljedeće raspone:

• East_Sales- A2: B6 na istočnom listu
• North_Sales- A2: B6 na sjevernom listu
• Južna_prodaja- A2: B6 na južnom listu
• West_Sales- A2: B6 na listu West

VLOOKUP i ugniježđeni IF-ovi

Ako imate razuman broj listova za pretraživanje, možete koristiti ugniježđene IF funkcije za odabir lista na temelju ključnih riječi u unaprijed definiranim ćelijama (stanice B1 do D1 u ovom slučaju).

S vrijednošću traženja u A2, formula je sljedeća:

• =VLOOKUP ($A2, IF (B$1=»istok», istok_prodaja, IF (B$1=»sjever», sjever_prodaja, IF (B$1=»jug», jug_prodaja, IF (B$1=»zapad», zapad_prodaja) ))), 2, NETOČNO)

Prevedeno na engleski, dio IF kaže:

Ako je B1 Istočno, pretražuje imenovani raspon Istok_Prodaja; ako je B1 Sjeverno, pretražuje raspon tzv Sjever_Prodaja; ako je B1 Jug, pretražuje raspon tzv Jug_Prodaja; a ako je B1 Zapadno, pretražuje raspon tzv West_Sales.

Raspon koji vraća IF ide table_array de VLOOKUP, koji izvlači odgovarajuću vrijednost iz drugog stupca u odgovarajućem listu.

Pametna upotreba mješovitih referenci za traženu vrijednost ($A2 – apsolutni stupac i relativni redak) i logički IF test (B$1 – relativni stupac i apsolutni redak) omogućuje kopiranje formule u druge ćelije bez ikakvih promjena – Excel automatski prilagođava reference na temelju relativnog položaja retka i stupca.

Dakle, unesete formulu u B2, kopirate je desno i dolje u onoliko stupaca i redaka koliko je potrebno, i dobit ćete sljedeći rezultat:

INDIREKTNI VLOOKUP

Kada radite s mnogo listova, više ugniježđenih razina može učiniti formulu predugom i teškom za čitanje. Mnogo bolji način je stvoriti a dinamički raspon vlookupa uz pomoć INDIRECT:

• =VLOOKUP ($A2, INDIRECT (B$1&»_Sales»), 2, FALSE)

Ovdje spajamo referencu na ćeliju koja sadrži jedinstveni dio imenovanog raspona (B1) i zajednički dio (_Prodajni). Ovo proizvodi tekstualni niz poput «Istok_Sales», koji INDIRECT pretvara u Excel-čitljiv naziv raspona.

Kao rezultat, dobivate kompaktnu formulu koja izvrsno funkcionira na bilo kojem broju listova:

Možda će vas zanimati sljedeće: Kako grupirati zaokretnu tablicu po mjesecima u Excelu

Kao što ćete vidjeti, ovo su načini korištenja Funkcija VLOOKUP u Excelu zajedno s drugim funkcijama pretraživanja po listu kao što je Vlookup koji vam može puno pomoći da pronađete podaci što želite postići između listova i datoteka u Excelu. Nadamo se da smo vam pomogli.