VLOOKUP (opis funkcije)

Koristite VLOOKUP kada morate pronaći stavke u tablici ili rasponu prema retku. Primjerice, potražite cijenu automobilskog dijela po broju dijela ili Pronađite naziv zaposlenika na temelju ID-a zaposlenika.

Savjet: Pogledajte ove videozapise s YouTubea iz programa Excel Community Experts za dodatnu pomoć za VLOOKUP!

Funkcija VLOOKUP u svojem najjednostavnijem obliku govori sljedeće:

= VLOOKUP (ono što želite potražiti, gdje ga želite potražiti, broj stupca u rasponu koji sadrži vrijednost koju želite vratiti, vraćanje približnog ili točnog podudaranja – 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: Tajna servisa VLOOKUP jest organiziranje podataka tako da je vrijednost koju tražite (voće) s desne strane vraćene 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 koje navedete u argumentu table_array .

Ako, primjerice, tablica – polje obuhvaća ćelije B2: D7, onda 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 nazive možete koristiti u argumentu umjesto referenci na ćelije. 

Prvi stupac u rasponu ćelija mora sadržavati lookup_value. Raspon ćelija mora uvrstiti 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 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 podudarnost-1/True pretpostavlja da je prvi stupac u tablici sortiran ili 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: i, 2; TRUE).

  • Točno podudaranje-0/FALSE traži točnu vrijednost u prvom stupcu. Na primjer, = VLOOKUP ("Smith"; a1: da, 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, navedete B2: D11 kao raspon, trebali biste brojati B 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 (vrijednost pretraživanja, raspon koji sadrži vrijednost pretraživanja, broj stupca u rasponu koji sadrži povratnu vrijednost, približnu podudarnost (TRUE) ili točno podudaranje (FALSE)).

Primjeri

Evo nekoliko primjera značajke VLOOKUP:

Primjer 1

VLOOKUP, primjer 1

Primjer 2

VLOOKUP, primjer 2

Primjer 3

VLOOKUP, primjer 3

Primjer 4

VLOOKUP, primjer 4

Peti primjer

VLOOKUP, primjer 5

VLOOKUP možete koristiti za kombiniranje više tablica u jednu, sve dok jedna tablica sadrži polja koja su zajednički sa svim drugim korisnicima. To može biti posebno korisno ako morate zajednički koristiti radnu knjigu s osobama koje imaju starije verzije programa Excel koje ne podržavaju podatkovne značajke s više tablica kao izvore podataka – kombiniranjem izvora u jednu tablicu i promjenom izvora podataka značajke podataka u novi tablica, značajka podataka može se koristiti u starijim verzijama programa Excel (pod uvjetom da je ta značajka podataka podržana u starijoj verziji).

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

Ovdje stupci A-F i H imaju vrijednosti ili formule koje koriste samo vrijednosti na radnom listu, a ostatak stupaca koristi VLOOKUP i vrijednosti stupca A (klijent kod) i stupac B (odvjetnik) da biste dobili podatke iz drugih tablica.

  1. Kopirajte tablicu s uobičajenim poljima na novi radni list i dodijelite joj naziv.

  2. Kliknite podaci > alati za podatke > odnosima da biste otvorili dijaloški okvir Upravljanje odnosima.

    Dijaloški okvir Upravljanje odnosima
  3. Za svaki navedeni odnos obratite pažnju na sljedeće:

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

    • Pridruženi naziv tablice vrijednosti. Ovo je table_array u formuli VLOOKUP.

    • Polje (stupac) u povezanoj tablici za pretraživanje koja sadrži željene podatke u novom stupcu. Te se informacije ne prikazuju u dijaloškom okviru Upravljanje odnosima – morat ćete pogledati povezanu tablicu za pretraživanje da biste vidjeli koje polje želite dohvatiti. Želite napomenuti broj stupca (A = 1) – to je col_index_num u formuli.

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

    U našem primjeru stupac G koristi odvjetnika ( lookup_value) da biste dobili podatke o stopi računa iz četvrtog stupca (col_index_num = 4) iz tablice radni list advokata, tblattorneyjevi ( table_array), s formulom = vlookup ([@Attorney], tbl_Attorneys, 4, FALSE).

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

  5. Nastavite dodavati polja dok ne budete imali sva polja koja su vam potrebna. Ako pokušavate pripremiti radnu knjigu koja sadrži podatkovne značajke koje koriste više tablica, promijenite izvor podataka podatkovne značajke 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 je col_index_num veći od broja stupaca u tablici – polje, dobit ćete #REF! #BROJ!.

Dodatne informacije o rješavanju #REF! pogreške u programu VLOOKUP potražite u članku ispravljanje #REF! pogreška.

#VALUE! u ćeliji

Ako je table_array manje od 1, dobit ćete #VALUE! #BROJ!.

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 #NAME! pogreška.

Pogreške #SPILL! u ćeliji

Ta posebna #SPILL! pogreška obično znači da se formula oslanja na implicitno sjecište za vrijednost pretraživanja i koristi cijeli stupac kao referencu. Na primjer, = VLOOKUP (A:a, a:c; 2; FALSE). Problem možete riješiti tako da usidrite referencu za pretraživanje s operatorom @ kao što je ovaj: = VLOOKUP (@A: A, a:c; 2; FALSE). Alternativno, možete koristiti tradicionalnu metodu VLOOKUP i uputiti 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 traženja brojeva ili vrijednosti datuma Provjerite nisu li podaci u prvom stupcu table_array spremljeni kao tekstne vrijednosti. U suprotnom, VLOOKUP može vratiti neispravnu 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 je RANGE_LOOKUP FALSE, a lookup_value je tekst, zamjenske znakove možete koristiti – upitnik (?) i asterisk (*) – 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 fontane s posljednjim slovima koje se mogu 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.

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Pogledajte i sljedeće

Kartica za brzu upotrebu: VLOOKUP osvježavačka
kartica za brzi pregled: VLOOKUP Savjeti
za otklanjanje poteškoćasa sustavom YouTube: VLOOKUP videozapisi iz programa Excel Community Experts
kako ispraviti #VALUE! pogreška u funkciji VLOOKUP
Ispravljanje #N/a pogreške u pregledu funkcije
VLOOKUP uformulama u programu Excel
kako izbjeći prekinute formule
Prepoznaj pogreške u formulama
Excel funkcije (abecednim)
funkcijama programa Excel (po kategorijama)
VLOOKUP (besplatni pretpregled)

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×