VLOOKUP (funkcija)

Primjenjuje se na
Excel za Microsoft 365 Excel za Microsoft 365 za Mac Excel 2024 Excel 2024 za Mac Excel 2021 Excel 2021 za Mac Excel 2019 Excel 2016

Savjet

Isprobajte novu funkciju XLOOKUP , poboljšanu verziju funkcije VLOOKUP koja funkcionira u bilo kojem smjeru i vraća točna podudaranja prema zadanim postavkama, što je čini jednostavnijom i praktičnijom za korištenje od prethodnika.

Funkciju VLOOKUP koristite kada trebate pronaći nešto u tablici ili rasponu prema retku. Pomoću nje, primjerice, dohvatite cijenu dijela za automobil prema broj 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 to želite potražiti, broj stupca u rasponu u kojem sadrži vrijednost koja se može vratiti, vraća približnu ili točno podudaranje – označeno kao 1/TRUE ili 0/FALSE)

Savjet

  • Tajna je funkcije VLOOKUP organizirati podatke na taj način da se vrijednost koju pretražujete (Voće) nalazi lijevo od povratne vrijednosti (Iznos) koju želite pronaći.
  • Ako ste pretplatnik na Microsoft Copilot, Copilot može još pojednostavniti umetanje i korištenje funkcija VLookup ili XLookup. Pogledajte Dohvaćanje uvida u podatke uz Copilot u programu Excel.

Tehničke pojedinosti

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 klijenta'! 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 ste naveli u argumentu table_array .
Na primjer, ako polje tablice obuhvaća ćelije B2:D7, lookup_value mora biti u stupcu B.
Lookup_value može biti vrijednost ili referenca na ćeliju.
polje_tablice (obavezno) Raspon ćelija koji će funkcija VLOOKUP tražiti za lookup_value i povratnu vrijednost. Možete koristiti imenovani raspon ili tablicu i u argumentu možete koristiti nazive umjesto referenci ćelija.
Prvi stupac u rasponu ćelija mora sadržavati lookup_value. Raspon ćelija mora sadržavati i povratnu vrijednost koju želite pronaći.
indeks_stupca (obavezno) Broj stupca (počevši od 1 za krajnji lijevi stupac 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žno podudaranje – 1/TRUE pretpostavlja da je prvi stupac tablice sortiran brojčano ili abecedno te će zatim traž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 se računa kao prvi stupac, C kao drugi i tako dalje.
  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(tražena vrijednost; raspon u kojem se nalazi tražena vrijednost; broj stupca unutar raspona u kojem se nalazi povratna vrijednost, približna vrijednost (TRUE) ili točno podudaranje (FALSE)).

Primjeri

Evo nekoliko primjera funkcije VLOOKUP:

Primjer 1

=VLOOKUP (B3;B2:E7;2;FALSE) VLOOKUP traži Jagodu u prvom stupcu (stupcu B) table_array B2:E7 i vraća imena Olivier 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 broj 102 (lookup_value) u drugom stupcu (stupcu B) raspona A2:C7 i vraća imena Fontana.

Primjer 3

=IF(VLOOKUP(103;A1:E7;2;FALSE)=Souse;Locirano;Nije pronađeno) IF provjerava vraća li VLOOKUP Sousa kao prezime zaposlenika koji odgovara broju 103 (lookup_value) u rasponu A1:E7 (table_array). Budući da je prezime koje odgovara broju 103 Leal, uvjet IF nije ispunjen te se prikazuje nepronađeno.

Primjer 4

=INT(YEARFRAC(DATE(2014;6;30);VLOOKUP(105;A2:E7;5;FLASE);1)) VLOOKUP traži datum rođenja zaposlenika koji odgovara datumu 109 (lookup_value) u rasponu A2:E7 (table_array) i vraća 04.03.1955. Zatim YEARFRAC oduzima taj datum rođenja od 30. 6. 2014. i vraća vrijednost koju INY zatim pretvara u cijeli broj 59.

Primjer 5

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, funkcija IF prikazat će prezime; u suprotnom funkcija IF vrati poruku Zaposlenik nije pronađen. ISNA će se pobrinuti da se funkcija VLOOKUP vrati #N/A, a ne s #N/A, pogreška zamjenjuje tekstom Zaposlenik nije pronađen. U ovom je primjeru vraćena vrijednost Burić, što je prezime koje odgovara broju 105.

Uobičajeni problemi

Problem Što nije uredu
Vraćena je vrijednost koja nije valjana Ako je range_lookup TRUE ili izostavljen, 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 range_lookup TRUE, a vrijednost u lookup_value manja je od najmanje vrijednosti u prvom stupcu table_array, dobit ćete vrijednost pogreške #N/A.
  • Ako je range_lookup 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 je col_index_num veći od broja stupaca u polju tablica, dobit ćete #REF! vrijednost nenumeričke prirode, PHI vraća vrijednost pogreške #VALUE!.
Dodatne informacije o rješavanju #REF! funkcije VLOOKUP potražite u članku Ispravljanje pogreške #REF!.
#VALUE! u ćeliji Ako je table_array manji 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 #VALUE! u funkciji VLOOKUP.
#NAZIV? u ćeliji #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 #NAZIV!
Pogreške #SPILL! u ćeliji Ova konkretna pogreška #SPILL! obično znači da se formula za vrijednost pretraživanja oslanja na implicitno sjecište te kao referencu koristi cijeli stupac. Na primjer, =VLOOKUP( A:A,A:C,2,FALSE). Problem možete riješiti tako da referencu za pretraživanje usidrite za operator @ ovako: =VLOOKUP(@A:A,A:C,2,FALSE). Možete i koristiti tradicionalnu metodu VLOOKUP i umjesto cijelog stupca pozvati se na jednu ćeliju: =VLOOKUP(A2,A:C,2,FALSE).

Praktični savjeti

Učinite ovo Razlog
Korištenje apsolutnih referenci za range_lookup 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. Kada pretražujete brojčane vrijednosti ili vrijednosti datuma, provjerite da podaci u prvom stupcu table_array nisu pohranjeni kao tekstne vrijednosti. U suprotnom bi VLOOKUP mogao vratiti neispravnu ili neočekivanu vrijednost.
Sortiranje prvog stupca Sortirajte prvi stupac table_array prije korištenja funkcije VLOOKUP kada je range_lookup TRUE.
Korištenje zamjenskih znakova Ako je range_lookup FALSE, a lookup_value je tekst, u lookup_value možete koristiti zamjenske znakove – upitnik (?) i zvjezdicu (*). 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) potražit će sve instance imena Jagoda s različitim posljednjim slovom.
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.

Je li vam potrebna dodatna pomoć?

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