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

Koristite funkciju VLOOKUP kada treba da pronađete nešto u tabeli ili u opsegu po redovima. Na primer, pronađite cenu dela automobila prema broju dela ili pronađite ime zaposlenog na osnovu ID-a zaposlenog.

U svom najjednostavnijem obliku, funkcija VLOOKUP glasi:

=VLOOKUP(ono što želite da potražite, gde želite da ga potražite, broj kolone u opsegu koja sadrži vrednost koja treba da se vrati, daje vrednost Približno ili Potpuno podudaranje – navedeno kao 1/TRUE ili 0/FALSE).

Pregledač ne podržava video zapis.

Savet: Tajna VLOOKUP je u tome što organizuje podatke tako da vrednost koju pronađete (voće) bude sa leve strane 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,TAČNO)

  • =VLOOKUP(„Fontić“,B2:E7,2,FALSE)

  • =VLOOKUP(A2,'Detalji klijenta'! A:F,3,FALSE)

Ime argumenta

Opis

vrednost_za_pronalaženje    (potrebno)

Vrednost koju želite da pronađete. Vrednost koju želite da potražite mora biti u prvoj koloni opsega ćelija koji ste naveli u argumentu table_array".

Na primer, ako niz tabela proteže ćelije B2:D7, onda lookup_value 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 od broja 1 za kolonu sa većinom levo 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 sortiranje po numeričko ili abecednom redu i tražiće 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("Smith",A1:B100,2,FALSE).

Kako da počnete

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

  1. Vrednost koju želite da potražite, koja se takođe naziva i vrednost za pronalaženje.

  2. 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.

  3. Broj kolone u opsegu koji sadrži povratnu vrednost. Na primer, ako navedete B2:D11 kao opseg, treba da računate B kao prvu kolonu, C kao drugu i tako dalje.

  4. 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 za VLOOKUP:

Primer 1

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

VLOOKUP traži Fontana u prvoj koloni (koloni B) u table_array B2:E7 i vraća Olivier iz druge kolone (kolona C) iz table_array.  False vraća 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 daje fontanu.

Primer 3

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

IF proverava da li VLOOKUP daje Sousa kao prezime zaposlenog korespoindinga na 103 (lookup_value) u A1:E7 (table_array). Pošto je prezime koje odgovara broju 103 Leal, prikazuje se IF uslov 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 04.03.1955. Zatim yearFRAC oduzima ovaj datum rođenja od datuma 2014/6/30 i daje vrednost, koja se zatim konvertuje u INY na cele brojeve 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 VLOOKUP daje vrednost za prezime iz kolone B za 105 (lookup_value). Ako VLOOKUP pronađe prezime, IF prikazuje prezime, u suprotnom IF vraća vrednost Zaposleni nije pronađen. ISNA je sigurni da ako VLOOKUP vrati #N/A, onda će grešku zameniti vrednost "Zaposleni nije pronađen" umesto #N/A.



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

Funkciju VLOOKUP možete da koristite za kombinovanje više tabela u jednu, dokle god jedna od tabela ima polja zajednička sa svim ostalima. To je 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 izvore 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 sistema Excel (pod uslovom da starija verzija podržava samu funkciju podataka).

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

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

  1. Kopirajte tabelu koja ima zajednička polja na novi radni list i dajte joj ime.

  2. Izaberite stavke Podaci > Alatke > relacije da biste otvorili dijalog Upravljanje relacijama.

    Dijalog "Upravljanje relacijama"
  3. Za svaku navedenu relaciju imajte u vezi sa sledećim stavkama:

    • Polje koje povezuje tabele (navedeno u zagradama u dijalogu). Ovo je lookup_value za vašu VLOOKUP formulu.

    • Ime srodne tabele za pronalaženje. Ovo je prvi table_array U VLOOKUP formuli.

    • Polje (kolona) u povezanoj tabeli za pronalaženje koja sadrži podatke koje želite u novoj koloni. Ove informacije se ne prikazžuju u dijalogu Upravljanje relacijama – morate da pogledate povezanu tabelu za pronalaženje da biste videli koje polje želite da preuzmete. Želite da imate utišan broj kolone (A=1) – ovo je col_index_num u formuli.

  4. 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 advokata (lookup_value) da bi pribavio podatke o ceni fakturisanja iz četvrte kolone (col_index_num = 4) iz tabele radnog lista Advokati, tblAttorneys (table_array),sa formulom =VLOOKUP([@Attorney],tbl_Attorneys;4,FALSE).

    Formula može da koristi i referencu ćelije i referencu opsega. U našem primeru to bi bilo =VLOOKUP(A2,'Advokati'! A:D,4,FALSE).

  5. Nastavite sa dodavanjem polja dok ne imate 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 kolona veća od broja kolona u nizu tabela, dobijate #REF! ako su argumenti datuma niske koje se ne mogu raščlaniti na važeće datume.

Dodatne informacije o rešavanju #REF! greške u VLOOKUP, pogledajte 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 temi Kako se ispravlja #NAME!.

#SPILL! u ćeliji

Ova određena #SPILL! obično znači da se formula oslanja na implicitni presek za vrednost za pronalaženje i korišćenje cele kolone kao reference. Na primer, =VLOOKUP( A:A,A:C,2,FALSE). Problem možete da rešite tako što ćete usidreni referencu za pronalaženje sa operatorom @ ovako: =VLOOKUP( @A:A,A:C,2,FALSE). Osim toga, možete da koristite tradicionalni VLOOKUP metod i upućuje 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 poslednjih slovom koje se može razlikovati.

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.

Potrebna vam je dodatna pomoć?

Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da dobijete podršku u Answers zajednici.

Takođe pogledajte

Kartica sa kratkim referencama: osvežilac za
VLOOKUP Kartica sa kratkim uputstvima: Saveti za rešavanje problema sa funkcijom
VLOOKUP Kako se ispravlja #VALUE! greška u funkciji VLOOKUP
Kako se ispravlja #N/A greške u funkciji VLOOKUP
Pregled formula u Excel
Kako da izbegnete neiskrene formule
Otkrivanje grešaka u formulama
Excel funkcije (abecednim slovima)
Excel funkcije (po kategoriji)
VLOOKUP (besplatan pregled)

Potrebna vam je dodatna pomoć?

Unapredite veštine
Istražite obuku

Da li su vam ove informacije koristile?

Koliko ste zadovoljni kvalitetom jezika?
Šta je uticalo na vaše iskustvo?

Hvala vam na povratnim informacijama!

×