Applies ToExcel za Microsoft 365 Excel za Microsoft 365 za Mac Excel za web Excel 2024 Excel 2024 za Mac Excel 2021 Excel 2021 za Mac Excel 2019 Excel 2019 za Mac Excel 2016

Savjet: Pokušajte koristiti novu funkciju XLOOKUP , poboljšanu verziju funkcije VLOOKUP koja funkcionira u bilo kojem smjeru i po zadanom vraća točne rezultate, što olakšava i praktičnije korištenje od prethodnika.

Funkciju VLOOKUP koristite kada morate pronaći stavke u tablici ili rasponu po retku. Primjerice, potražite cijenu dijela automobila prema broju dijela ili pronađite ime zaposlenika na temelju ID-a zaposlenika.

Funkcija VLOOKUP u svojem najjednostavnijem obliku govori sljedeće:

=VLOOKUP(Što želite potražiti, gdje ga želite potražiti, broj stupca u rasponu koji sadrži vrijednost za vraćanje, vraćanje približnog ili točnog podudaranja – što je naznačeno kao 1/TRUE ili 0/FALSE).

Vaš preglednik ne podržava videozapise. Instalirajte Microsoft Silverlight, Adobe Flash Player ili Internet Explorer 9.

Savjet: Funkcija VLOOKUP tajna je organiziranja podataka tako da vrijednost koju potražite (Voće) s lijeve strane povratne vrijednosti (iznosa) koju želite pronaći.

Funkcija VLOOKUP dohvatit će vrijednost iz tablice.

Sintaksa 

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

Na primjer:

  • =VLOOKUP(A2;A10:C20;2;TRUE)

  • =VLOOKUP("Jagoda",B2:E7,2,FALSE)

  • =VLOOKUP(A2;'Detalji o klijentu'! A:F,3,FALSE)

Naziv argumenta

Opis

vrijednost_pretraživanja    (obavezno)

Vrijednost koju želite potražiti. Vrijednost koju želite potražiti mora biti u prvom stupcu raspona ćelija koji navedete u argumentu table_array .

Ako, primjerice, polje tablice obuhvaća ćelije B2:D7, lookup_value mora biti u stupcu B.

Vrijednost_pretraživanja može biti vrijednost ili referenca na ćeliju.

polje_tablice    (obavezno)

Raspon ćelija koji će funkcija VLOOKUP tražiti za vrijednost_pretraživanja i povratnu vrijednost. Možete koristiti imenovani raspon ili tablicu, a umjesto referenci ćelija možete koristiti nazive u argumentu. 

Prvi stupac u rasponu ćelija mora sadržavati lookup_value. Raspon ćelija mora obuhvaćati i povratnu vrijednost koju želite pronaći.

Saznajte kako odabrati raspone na radnom listu.

indeks_stupca    (obavezno)

Broj stupca (počevši od 1 za lijevi stupac stupca table_array) koji sadrži povratnu vrijednost.

raspon_pretraživanja    (neobavezno)

Logička vrijednost koja određuje želite li da VLOOKUP pronađe približnu vrijednost ili točno podudaranje:

  • Približna vrijednost - 1/TRUE pretpostavlja da je prvi stupac u tablici sortiran numerički ili abecednim redom, a zatim će potražiti najbližu vrijednost. To je zadana metoda ako sami ne navedete drugu. Na primjer, =VLOOKUP(90,A1:B100,2,TRUE).

  • Točno podudaranje – 0/FALSE traži točnu vrijednost u prvom stupcu. Na primjer, =VLOOKUP("Smith",A1:B100,2,FALSE).

Početak rada

Da biste sastavili sintaksu VLOOKUP, potrebna su vam četiri podatka:

  1. Vrijednost koju želite dohvatiti, koja se naziva i vrijednost pretraživanja.

  2. Raspon u kojem se nalazi vrijednost koju želite dohvatiti. Ne zaboravite da se vrijednost koju želite dohvatiti mora nalaziti u prvom stupcu raspona da bi funkcija VLOOKUP pravilno funkcionirala. Ako je vrijednost koju želite dohvatiti, primjerice, u ćeliji C2, raspon bi trebao počinjati sa stupcem C.

  3. Broj stupca u rasponu koji sadrži vrijednost rezultata. Ako, primjerice, kao raspon navedete B2:D11, B biste trebali brojati kao prvi stupac, C kao drugi itd.

  4. Možete i navesti TRUE ako želite približnu vrijednost ili pak FALSE ako želite dohvatiti vrijednost koja se točno podudara s vrijednošću rezultata. Ako ništa ne navedete, zadana će vrijednost biti TRUE odnosno približna vrijednost.

Sad sve navedene podatke posložite na sljedeći način:

=VLOOKUP(vrijednost pretraživanja, raspon koji sadrži vrijednost pretraživanja, broj stupca u rasponu koji sadrži povratnu vrijednost, približnu vrijednost (TRUE) ili Točno podudaranje (FALSE)).

Primjeri

Evo nekoliko primjera funkcije VLOOKUP:

Primjer 1

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

VLOOKUP traži Fontanu u prvom stupcu (stupac B) u ćeliji table_array B2:E7, a zatim vraća Vlookup iz drugog stupca (stupca C) table_array.  False vraća točno podudaranje.

Primjer 2

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

VLOOKUP traži točno podudaranje (FALSE) prezimena za 102 (lookup_value) u drugom stupcu (stupac B) u rasponu A2:C7 i vraća Fontanu.

Primjer 3

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

IF provjerava vraća li VLOOKUP Sousa kao prezime zaposlenika koje se odnosi na 103 (lookup_value) u rasponu A1:E7 (table_array). Budući da je prezime koje odgovara 103 Leal, uvjet IF je false i prikazuje se Not Found.

Primjer 4

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

VLOOKUP traži datum rođenja zaposlenika koji odgovara 109 (lookup_value) u rasponu A2:E7 (table_array) i vraća vrijednost 03/04/1955. Zatim YEARFRAC oduzima taj datum rođenja od 6. 6. 2014. 2014. i vraća vrijednost koju inY zatim pretvara u cijeli broj 59.

Peti primjer

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

IF provjerava vraća li VLOOKUP vrijednost za prezime iz stupca B za 105 (lookup_value). Ako VLOOKUP pronađe prezime, if će prikazati prezime, u suprotnom IF vraća Vrijednost zaposlenika nije pronađena. ISNA jamči da će, ako funkcija VLOOKUP #N/A, zamijeniti pogrešku Zaposlenik nije pronađena, #N/A.



U ovom primjeru povratna vrijednost je Burke, što je prezime koje odgovara 105.

Funkciju VLOOKUP možete koristiti za kombiniranje više tablica u jednu, sve dok jedna od tablica ima polja zajednička svim ostalima. To može biti osobito korisno ako radnu knjigu morate zajednički koristiti s osobama koje imaju starije verzije programa Excel koje ne podržavaju značajke podataka s više tablica kao izvora podataka – kombiniranjem izvora u jednu tablicu i promjenom izvora podataka značajke podataka u novu tablicu, značajka podataka može se koristiti u starijim verzijama programa Excel (pod uvjetom da je sama značajka podataka podržana u starijoj verziji).

Radni list sa stupcima koji koriste VLOOKUP za dohvaćanje podataka iz drugih tablica

Ovdje stupci A - F i H sadrže vrijednosti ili formule koje koriste samo vrijednosti na radnom listu, a ostali stupci koriste VLOOKUP i vrijednosti stupca A (Klijentski kod) i stupca B (odvjetnik) da bi dobili podatke iz drugih tablica.

  1. Kopirajte tablicu koja sadrži zajednička polja na novi radni list i dodijelite joj naziv.

  2. Kliknite Alati > podataka >odnose da biste otvorili Upravljanje odnosima dijaloškog okvira.

    The Upravljanje odnosima dialog box
  3. Za svaki navedeni odnos imajte na umu sljedeće:

    • Polje koje povezuje tablice (navedeno u zagradama u dijaloškom okviru). To je lookup_value za formulu VLOOKUP.

    • Naziv povezane tablice s vrijednostima. To je table_array formuli VLOOKUP.

    • Polje (stupac) u povezanoj tablici s vrijednostima koje sadrži željene podatke u novom stupcu. Ti se podaci ne prikazuju Upravljanje odnosima dijaloškom okviru – morat ćete pogledati povezanu tablicu s vrijednostima da biste vidjeli koje polje želite dohvatiti. Želite zabilježiti broj stupca (A=1) – to je col_index_num u formuli.

  4. Da biste dodali polje u novu tablicu, u prvi prazni stupac unesite formulu VLOOKUP pomoću informacija prikupljenih u 3. koraku.

    U našem primjeru stupac G koristi odvjetnika ( lookup_value) za dohvaćanje podataka o naplati iz četvrtog stupca (col_index_num = 4) iz tablice radnog lista Odvjetnik, tblAttorneys ( table_array), s formulom =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

    Formula može koristiti i referencu ćelije i referencu raspona. U našem primjeru to bi bilo =VLOOKUP(A2,'Attorneys'! A:D,4,FALSE).

  5. Nastavite dodavati polja dok ne otvorite sva potrebna polja. Ako pokušavate pripremiti radnu knjigu koja sadrži podatkovne značajke koje koriste više tablica, promijenite izvor podataka značajke podataka u novu tablicu.

Problem

Što nije uredu

Vraćena je vrijednost koja nije valjana

Ako je vrijednost range_lookup TRUE ili izostavljena, prvi se stupac mora sortirati abecedno ili brojčano. Ako prvi stupac nije sortiran, povratna vrijednost mogla bi biti nešto što ne očekujete. Sortirajte prvi stupac ili upotrijebite FALSE da biste dobili točno podudaranje.

#N/A u ćeliji

  • Ako je vrijednost argumenta raspon_pretraživanja TRUE, a vrijednost_pretraživanja manja je od najmanje vrijednosti u prvom stupcu polja_tablice, dobit ćete vrijednost pogreške #N/A.

  • Ako je vrijednost argumenta raspon_pretraživanja FALSE, vrijednost pogreške #N/A upućuje na to da identičan broj nije pronađen.

Dodatne informacije o uklanjanju pogrešaka #N/D funkcije VLOOKUP potražite u članku Ispravljanje pogreške #N/D u funkciji VLOOKUP.

#REF! u ćeliji

Ako col_index_num veći od broja stupaca u polju tablice , dobit ćete #REF! vrijednost nenumeričke prirode, PHI vraća vrijednost pogreške #VALUE!.

Dodatne informacije o rješavanju #REF! u funkciji VLOOKUP potražite u članku Ispravljanje pogreške #REF!.

#VALUE! u ćeliji

Ako je table_array manje od 1, dobit ćete #VALUE! vrijednost nenumeričke prirode, PHI vraća vrijednost pogreške #VALUE!.

Dodatne informacije o uklanjanju pogrešaka #VRIJEDNOST! funkcije VLOOKUP potražite u članku Ispravljanje pogreške #VRIJEDNOST! u funkciji VLOOKUP.

#NAME? u ćeliji

Vrijednost pogreške #NAME? obično upućuje na to da u formuli nedostaju navodnici. Da biste potražili ime neke osobe, u formuli ga navedite unutar navodnika. Na primjer, unesite ime kao "Jagoda" u formulu =VLOOKUP("Jagoda",B2:E7,2,FALSE).

Dodatne informacije potražite u članku Ispravljanje pogreške #NAME!.

Pogreške #SPILL! u ćeliji

Ova konkretna #SPILL! pogreška obično znači da se formula poziva na implicitno sjecište vrijednosti pretraživanja i koristi cijeli stupac kao referencu. Na primjer, =VLOOKUP(A:A,A:C,2,FALSE). Problem možete riješiti sidrenjem reference pretraživanja pomoću operatora @ ovako: =VLOOKUP(@A:A,A:C,2,FALSE). Možete koristiti i tradicionalnu metodu VLOOKUP i referirati se na jednu ćeliju umjesto na cijeli stupac: =VLOOKUP(A2,A:C,2,FALSE).

Postupak

Razlog

Korištenje apsolutnih referenci za raspon_pretraživanja

Korištenje apsolutnih referenci omogućuje ispunjavanje formule prema dolje tako da uvijek traži u točno istom rasponu za traženje.

Saznajte kako koristiti apsolutne reference ćelija.

Ne pohranjujte brojčane vrijednosti ni vrijednosti datuma kao tekst.

Prilikom pretraživanja vrijednosti brojeva ili datuma provjerite nisu li podaci u prvom stupcu table_array pohranjeni kao tekstne vrijednosti. U suprotnom vlookup može vratiti netočnu ili neočekivanu vrijednost.

Sortiranje prvog stupca

Sortirajte prvi stupac polja_tablice prije korištenja funkcije VLOOKUP kada je vrijednost argumenta raspon_pretraživanja TRUE.

Korištenje zamjenskih znakova

Ako range_lookup false , lookup_value je tekst, možete koristiti zamjenske znakove – upitnik (?) i zvjezdicu (*)– u lookup_value. Upitnik odgovara bilo kojem pojedinačnom znaku. Zvjezdica odgovara bilo kojem nizu znakova. Ako želite pronaći stvarni upitnik ili zvjezdicu, upišite tildu (~) ispred znaka.

Na primjer, =VLOOKUP("Fontan?",B2:E7,2,FALSE) tražit će sve instance Fonta s posljednjim slovom koje se može razlikovati.

Podaci ne smiju sadržavati pogrešne znakove.

Prilikom pretraživanja tekstnih vrijednosti u prvom stupcu provjerite ne sadrže li podaci u njemu početne razmake, završne razmake, nedosljedno korištenje ravnih ( ' ili " ) i kosih ( ‘ ili “) navodnika ili nestandardne znakove. U tim slučajevima VLOOKUP može vratiti neočekivanu vrijednost.

Da biste dobili točne rezultate, pomoću funkcije CLEAN ili funkcije TRIM uklonite završne razmake iza vrijednosti u ćelijama.

Treba li vam dodatna pomoć?

Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.

Dodatne informacije

XLOOKUP funkcija

Videozapis: Kada i kako koristiti VLOOKUP

Kartica za brzi pregled: podsjetnik za VLOOKUP

Ispravljanje pogreške #N/A u funkciji VLOOKUP

Dohvaćanje vrijednosti pomoću funkcija VLOOKUP, INDEX i MATCH

HLOOKUP

Potrebna vam je dodatna pomoć?

Želite dodatne mogućnosti?

Istražite pogodnosti pretplate, pregledajte tečajeve za obuku, saznajte kako zaštititi uređaj i još mnogo toga.

Zajednice vam pomažu da postavljate pitanja i odgovarate na njih, pošaljete povratne informacije i čujete se sa stručnjacima s bogatim znanjem.