VLOOKUP (funkcija VLOOKUP)

Savet: Pokušajte da koristite novu XLOOKUP funkciju, poboljšanu verziju funkcije VLOOKUP koja radi u bilo kom smeru i podrazumevano vraća tačna podudaranja, što olakšava korišćenje od prethodnika.

Funkciju VLOOKUP koristite kada treba da pronađete stavke u tabeli ili opsegu po redu. Na primer, potražite cenu dela za automobil po broju dela ili pronađite ime zaposlenog na osnovu ID-a zaposlenog.

U svom najjednostavnijem obliku, funkcija VLOOKUP glasi:

=VLOOKUP(Šta želite da pronađete, gde želite da ga potražite, broj kolone u opsegu koji sadrži vrednost koja će biti vraćena, vraća približno ili potpuno podudaranje – označeno sa 1/TRUE ili 0/FALSE).

Pregledač ne podržava video. Instalirajte Microsoft Silverlight, Adobe Flash Player ili Internet Explorer 9.

Savet: Tajna funkcije VLOOKUP je u tome da organizujete podatke tako da vrednost koju tražite (Voće) bude levo od povratne vrednosti (Iznos) koju želite da pronađete.

Koristite funkciju VLOOKUP da biste potražili vrednost u tabeli.

Sintaksa

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Na primer:

=VLOOKUP(A2,A10:C20,2,TRUE)
=VLOOKUP(„Fontić“,B2:E7,2,FALSE)
=VLOOKUP(A2,'Detalji o klijentu'! A:F,3,FALSE)

Ime argumenta	Opis
vrednost_za_pronalaženje (potrebno)	Vrednost koju želite da pronađete. Vrednost koju želite da pronađete mora biti u prvoj koloni opsega ćelija koji navedete *u argumentu table_array* . Na primer, ako *niz tabela* proteže ćelije B2:D7, onda lookup_value u koloni B. *Vrednost_za_pronalaženje* može da bude vrednost ili referenca na ćeliju.
table_array (potrebno)	Opseg ćelija koji će funkcija VLOOKUP tražiti za *lookup_value* i povratnu vrednost. Možete da koristite imenovani opseg ili tabelu i možete da koristite imena u argumentu umesto referenci ćelija. Prva kolona u opsegu ćelija mora da sadrži *lookup_value*. Opseg ćelija takođe mora da sadrži povratnu vrednost koju želite da pronađete. Saznajte kako da izaberete opsege na radnom listu.
indeksni_broj_kolone (potrebno)	Broj kolone (počevši od broja 1 za kolonu sa *najviše leve strane u koloni table_array*) koja sadrži povratnu vrednost.
opseg_za_pronalaženje (opcionalno)	Logička vrednost koja navodi da li želite da VLOOKUP pronađe približno ili potpuno podudaranje: Približno podudaranje - 1/TRUE pretpostavlja da je prva kolona u tabeli sortirana numerički ili abecednim redom, a zatim će tražiti najbližu vrednost. Ovo je podrazumevani metod ako ne navedete neki drugi. Na primer, =VLOOKUP(90,A1:B100,2,TRUE). Potpuno podudaranje - 0/FALSE traži tačnu vrednost u prvoj koloni. Na primer, =VLOOKUP("Simić",A1:B100,2,FALSE).

Kako da počnete

Biće vam potrebne četiri informacije da biste mogli da napravite VLOOKUP sintaksu:

Vrednost koju želite da potražite, koja se takođe naziva i vrednost za pronalaženje.
Opseg u kojem se nalazi vrednost za pronalaženje. Ne zaboravite da vrednost za pronalaženje treba uvek da bude u prvoj koloni u opsegu da bi funkcija VLOOKUP radila ispravno. Na primer, ako se vrednost za pronalaženje nalazi u ćeliji C2, opseg bi trebalo da počne sa C.
Broj kolone u opsegu koji sadrži povratnu vrednost. Na primer, ako kao opseg navedete B2:D11, trebalo bi da računate B kao prvu kolonu, C kao drugu i tako dalje.
Opcionalno, možete da navedete TRUE ako želite približno podudaranje ili FALSE ako želite potpuno podudaranje povratne vrednosti. Ako ne navedete ništa, podrazumevana vrednost će uvek biti TRUE ili približno podudaranje.

Sad sastavite sve gorenavedeno na sledeći način:

=VLOOKUP(vrednost za pronalaženje, opseg koji sadrži vrednost za pronalaženje, broj kolone u opsegu koja sadrži povratnu vrednost, Približno podudaranje (TRUE) ili Potpuno podudaranje (FALSE)).

Primeri

Evo nekoliko primera funkcije VLOOKUP:

Primer 1

=VLOOKUP (B3,B2:E7,2,FALSE)

Funkcija VLOOKUP traži Fontanu u prvoj koloni (koloni B) u table_array B2:E7 i vraća Olivija iz druge kolone (kolone C) table_array. False daje potpuno podudaranje.

Primer 2

=VLOOKUP (102,A2:C7,2,FALSE)

VLOOKUP traži potpuno podudaranje (FALSE) prezimena za 102 (lookup_value) u drugoj koloni (koloni B) u opsegu A2:C7 i vraća fontanu.

Primer 3

=IF(VLOOKUP(103,A1:E7,2,FALSE)="Souse","Located","Not found")

IF proverava da li funkcija VLOOKUP kao prezime zaposlenog vraća rezultat 103 (lookup_value) u ćelijama A1:E7 (table_array). Pošto je prezime koje odgovara broju 103 Leal, prikazuje se IF uslov netačan i nije pronađeno.

4. primer

=INT(YEARFRAC(DATE(2014,6,30),VLOOKUP(105,A2:E7,5,FLASE),1))

VLOOKUP traži datum rođenja zaposlenog koji odgovara 109 (lookup_value) u opsegu A2:E7 (table_array) i daje 03.04.1955. Zatim funkcija YEARFRAC oduzima ovaj datum rođenja od datuma 6.2014.2014. i vraća vrednost koja se zatim konvertuje pomoću FUNKCIJE INY u ceo broj 59.

Primer 5

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,"Zaposleni nije pronađen",VLOOKUP(105,A2:E7,2,FALSE))

IF proverava da li funkcija VLOOKUP vraća vrednost za prezime iz kolone B za 105 (lookup_value). Ako funkcija VLOOKUP pronađe prezime, IF će prikazati prezime, u suprotnom IF daje vrednost Zaposleni nije pronađen. ISNA obezbeđuje da, ako funkcija VLOOKUP vrati #N/A, onda će grešku zameniti zaposleni koji nije pronađen, a ne #N/A.

U ovom primeru, povratna vrednost je Burke, što je prezime koje odgovara broju 105.

Funkciju VLOOKUP možete da koristite da biste kombinovali više tabela u jednu, pod uslovom da jedna od tabela ima polja zajednička sa svim ostalim tabelama. To može biti naročito korisno ako treba da delite radnu svesku sa osobama koje imaju starije verzije programa Excel koje ne podržavaju funkcije podataka sa više tabela kao izvorima podataka – kombinovanjem izvora u jednu tabelu i promenom izvora podataka funkcije podataka u novu tabelu, funkcija podataka može da se koristi u starijim verzijama programa Excel (pod uslovom da starija verzija podržava samu funkciju podataka).

Radni list sa kolonama koje koriste funkciju VLOOKUP za preuzimanje podataka iz drugih tabela

Ovde kolone A-F i H imaju vrednosti ili formule koje koriste samo vrednosti na radnom listu, a ostale kolone koriste funkciju VLOOKUP i vrednosti kolone A (kôd klijenta) i kolonu B (advokat) za preuzimanje podataka iz drugih tabela.

Kopirajte tabelu koja ima zajednička polja na novi radni list i dajte joj ime.
Izaberite stavku Alatke > podataka > relacije da biste otvorili dijalog Upravljanje relacijama.
Za svaku navedenu relaciju imajte na sledeće načine:
- Polje koje povezuje tabele (navedeno u zagradama u dijalogu). Ovo je lookup_value za VLOOKUP formulu.
- Ime srodne tabele za pronalaženje. Ovo je table_array U VLOOKUP formuli.
- Polje (kolona) u povezanoj tabeli za pronalaženje koje sadrži podatke koje želite u novoj koloni. Ove informacije se ne prikazuju u dijalogu Upravljanje relacijama – moraćete da pogledate srodnu tabelu za pronalaženje da biste videli koje polje želite da preuzmete. Želite da obratite napomenu o broju kolone (A=1) – to je col_index_num u formuli.
Da biste dodali polje u novu tabelu, unesite VLOOKUP formulu u prvu praznu kolonu pomoću informacija koje ste prikupili u 3. koraku.

U našem primeru, kolona G koristi advokat ( lookup_value) za dobijanje podataka o stopi naplate iz četvrte kolone (col_index_num = 4) iz tabele Radnog lista "Advokati" tblAttorneys ( table_array) sa formulom =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

Formula takođe može da koristi referencu ćelije i referencu opsega. U našem primeru to bi bila =VLOOKUP(A2,'Advokati'! A:D,4,FALSE).
Nastavite da dodajete polja dok ne budete imali sva polja koja su vam potrebna. Ako pokušavate da pripremite radnu svesku koja sadrži funkcije podataka koje koriste više tabela, promenite izvor podataka funkcije podataka u novu tabelu.

Problem	U čemu je problem
Vraćena je pogrešna vrednost	Ako je *opseg_za_pronalaženje* TRUE ili ako ostane prazan, prvu kolonu treba sortirati po abecednom ili numeričkom redosledu. Ako ne sortirate prvu kolonu, povratna vrednost će možda biti nešto što niste očekivali. Sortirajte prvu kolonu ili koristite funkciju FALSE za tačno podudaranje.
Greška #N/A u ćeliji	Ako je *opseg_za_pronalaženje* TRUE, onda ćete dobiti vrednost greške #N/A ako je vrednost u *vrednosti_za_pronalaženje* manja od najmanje vrednosti u prvoj koloni tabele *niza_tabele. Ako je opseg_za_pronalaženje* FALSE, vrednost #N/A greške označava da tačan broj nije pronađen. Više informacija o otklanjanju grešaka #N/A u funkciji VLOOKUP potražite u članku Ispravljanje greške #N/A u funkciji VLOOKUP.
Greška #REF! u ćeliji	Ako *col_index_num* veće od broja kolona u nizu tabela , dobijate #REF! ako su argumenti datuma niske koje se ne mogu raščlaniti na važeće datume. Za više informacija o rešavanju problema #REF! greške u funkciji VLOOKUP, pročitajte članak Kako se ispravlja #REF! greške.
Greška #VALUE! u ćeliji	Ako *je table_array* manja od 1, dobijate #VALUE! ako su argumenti datuma niske koje se ne mogu raščlaniti na važeće datume. Više informacija o otklanjanju grešaka #VALUE! u funkciji VLOOKUP potražite u članku Ispravljanje greške #VALUE! u funkciji VLOOKUP.
#NAME? u ćeliji	Vrednost greške #NAME? obično znači da u formuli nedostaju citati. Da biste potražili ime osobe, obavezno koristite znake navoda oko imena u formuli. Na primer, unesite ime kao "Fontana" u formuli =VLOOKUP("Fontana",B2:E7,2,FALSE). Više informacija potražite u članku Ispravljanje greške #NAME!..
#SPILL! u ćeliji	Ova određena #SPILL! greška obično znači da se formula oslanja na implicitni presek za vrednost za pronalaženje i da koristi celu kolonu kao referencu. Na primer, =VLOOKUP(A:A,A:C,2,FALSE). Problem možete da rešite tako što ćete uključiti referencu za pronalaženje sa operatorom @ na sledeći način: =VLOOKUP(@A:A,A:C,2,FALSE). Osim toga, možete da koristite tradicionalni VLOOKUP metod i upućujte na jednu ćeliju umesto na celu kolonu: =VLOOKUP(A2,A:C,2,FALSE).

Uradite sledeće	Zašto
Korišćenje apsolutnih referenci za opseg_za_pronalaženje	Korišćenje apsolutnih referenci vam omogućava da popunite formulu tako da uvek gleda u potpuno isti opseg za pronalaženje. Saznajte kako da koristite apsolutne reference na ćeliju.
Nemojte skladištiti brojeve i datume kao tekstualne vrednosti.	Prilikom traženja brojeva ili datuma, proverite da li su podaci u prvoj koloni argumenta *niz_tabele* uskladišteni kao tekstualne vrednosti. U ovom slučaju, funkcija VLOOKUP može dati netačnu ili neočekivanu vrednost.
Sortiranje prve kolone	Sortirajte prvu kolonu *table_array* pre upotrebe funkcije VLOOKUP kada je range_lookup TRUE .
Korišćenje džoker znakova	Ako je argument *range_lookup* FALSE, a argument *lookup_value* je tekst, možete da koristite džoker znakove – znak pitanja (?) i zvezdicu () – u argumentu lookup_value. Znak pitanja se podudara sa bilo kojim znakom. Zvezdica se podudara sa bilo kojim nizom znakova. Ako želite da pronađete pravi znak pitanja ili zvezdicu, otkucajte znak tilda (~) ispred traženog znaka. Na primer, =VLOOKUP("Fontan?",B2:E7,2,FALSE) će tražiti sve instance fontane* sa poslednjim slovom koje može da se razlikuje.
Uverite se da podaci ne sadrže pogrešne znakove.	Prilikom pretrage tekstualnih vrednosti u prvoj koloni, uverite se da podaci u prvoj koloni ne sadrže razmake na početku ili na kraju, znakove koji neće biti odštampani, kao i da se pravi ( ' ili " ) i zakrivljeni ( ‘ ili “) navodnici dosledno upotrebljavaju. U suprotnom, funkcija VLOOKUP može dati neočekivanu vrednost. Da biste dobili precizne rezultate, probajte da koristite funkciju CLEAN ili funkciju TRIM da biste uklonili razmake na kraju nakon vrednosti tabele u ćeliji.