VLOOKUP

Traži vrednost u krajnjoj levoj koloni tabele i zatim daje vrednost u istom redu naredne kolone u tabeli.

Znak V u nazivu funkcije VLOOKUP označava „vertikalno". Kada se vrednosti koje se porede nalaze u koloni levo u odnosu na podatak koji tražite, umesto funkcije HLOOKUP upotrebite funkciju VLOOKUP.

Sintaksa

VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)

Lookup_value    Vrednost za pretraživanje prve kolone niza (niz: koristi se za pravljenje formula koje daju više rezultata ili koje koriste grupu argumenata uređenih u redove i kolone. Opseg nizova ima zajedničku formulu; niz konstanti je grupa konstanti koje se koriste kao argumenti.) tabele. Lookup_value može da bude vrednost ili referenca. Ako je lookup_value manja od najmanje vrednosti iz prve kolone tabele table_array, VLOOKUP daje grešku #N/A.

Table_array    Dve ili više kolona podataka. Koristite referencu na opseg ili ime opsega. Vrednosti u prvoj koloni argumenta table_array su vrednosti tražene argumentom lookup_value. Ove vrednosti mogu da budu predstavljene kao tekst, brojevi ili logičke vrednosti. Velika i mala slova su ekvivalentna.

Col_index_num    Broj kolone u tabeli table_array iz koje mora da se dobije istovetna vrednost. Ako se za col_index_num zada vrednost 1, dobija se vrednost iz prve kolone tabele table_array; ako je col_index_num vrednosti 2, dobija se vrednost iz druge kolone tabele table_array i tako dalje. Ako je col_index_num:

• Manji od 1, VLOOKUP daje grešku #VALUE!.
• Veći od broja kolona u tabele table_array, VLOOKUP daje grešku #REF!.

Range_lookup    Logička vrednost koja precizira da li želite da VLOOKUP pronađe istovetnu ili približnu vrednost:

• Ako ovaj argument ima vrednost TRUE ili je izostavljen, dobija se istovetna ili približna vrednost. Ako se istovetna vrednost ne pronađe, dobija se sledeća najveća vrednost manja od vrednosti lookup_value.

Vrednosti u prvoj koloni tabele table_array moraju biti raspoređene u rastućem redosledu; u suprotnom, VLOOKUP možda neće dati tačnu vrednost. Vrednosti možete da postavite u rastući redosled tako što ćete u meniju Podaci da izaberete stavku Sortiraj, a zatim da izaberite Rastući redosled. Za više informacija pogledajte Podrazumevani redosledi sortiranja.

• Ako je vrednost ovog argumenta FALSE, funkcija VLOOKUP će pronaći samo istovetnu vrednost. U ovom slučaju vrednosti u prvoj koloni tabele table_array ne moraju da se sortiraju. Ako postoje dve ili više vrednosti u prvoj koloni tabele table_array koje se podudaraju sa lookup_value, koristi se vrednost koja je prva pronađena. Ako se istovetna vrednost ne pronađe, dobija se greška #N/A.

Primedbe

• Pri traženju tekstualnih vrednosti u prvoj koloni tabele table_array, uverite se da podaci u prvoj koloni te tabele ne sadrže razmake na početku ili na kraju, proverite da li se dosledno koriste obični ( ' ili " ) i tipografski navodnici ( ‘ ili “), odnosno znakovi koji se ne štampaju. U tim slučajevima, funkcija VLOOKUP može da dâ netačne ili neočekivane vrednosti. Za više informacija o funkcijama koje možete da koristite za prečišćavanje tekstualnih podataka pogledajte Funkcije za tekst i podatke.
• Pri traženju vrednosti brojeva i datuma, uverite se da podaci u prvoj koloni tabele table_array nisu sačuvani kao tekstualne vrednosti. U tom slučaju, VLOOKUP može da dâ netačnu ili neočekivanu vrednost. Za više informacija pogledajte Pretvaranje brojeva uskladištenih kao tekst u brojeve.
• Ako je vrednost argumenta range_lookup FALSE i ako je argument lookup_value tekst, možete koristiti džokere, upitnik (?) i zvezdicu (*) u argumentu lookup_value. Upitnik odgovara bilo kojem znaku; zvezdica odgovara skupu znakova. Ukoliko želite da pronađete određeni upitnik ili zvezdicu, otkucajte znak tilda (~) ispred znaka.

Primer 1

Primer ćete lakše razumeti ako ga iskopirate u prazan radni list.

Kako se kopira primer

U ovom primeru se pretražuje kolona „Gustina“ tabele sa atmosferskim podacima, da bi se pronašle odgovarajuće vrednosti u kolonama „Viskozitet“ i „Temperatura“. (Date su vrednosti pri temperaturi vazduha od 0º C na nivou mora, odnosno pritisku od 1 atmosfere.)

1 2 3 4 5 6 7 8 9 10

A B C Gustina Viskozitet Temperatura 0,457 3,55 500 0,525 3,25 400 0,616 2,93 300 0,675 2,75 250 0,746 2,57 200 0,835 2,38 150 0,946 2,17 100 1,09 1,95 50 1,29 1,71 0 Formula Opis (rezultat) =VLOOKUP(1;A2:C10;2) Koristeći približnu vrednost, traži vrednost 1 u koloni A, pronalazi najveću vrednost manju od ili jednaku 1 u koloni A, a to je 0,946 i zatim daje vrednost iz kolone B u istom tom redu. (2.17) =VLOOKUP(1;A2:C10;3;TRUE) Koristeći približnu vrednost, traži vrednost 1 u koloni A, pronalazi najveću vrednost manju od ili jednaku 1 u koloni A, a to je 0,946 i zatim daje vrednost iz kolone C u istom tom redu. (100) =VLOOKUP(,7;A2:C10;3;FALSE) Koristeći istovetnu vrednost, traži vrednost 7 u koloni A. Pošto u koloni A ne postoji istovetna vrednost, dobija se greška (#N/A) =VLOOKUP(0,1;A2:C10;2;TRUE) Koristeći približnu vrednost, traži vrednost 0,1 u koloni A. Pošto je vrednost 0,1 manja od najmanje vrednosti u koloni A, dobija se greška (#N/A) =VLOOKUP(2;A2:C10;2;TRUE) Koristeći istovetnu vrednost, traži vrednost 2 u koloni, pronalazi najveću vrednost manju od ili jednaku 2 u koloni A, a to je 1,29 i zatim daje vrednost iz kolone B u istom tom redu. (1,71)

Primer 2

Primer ćete lakše razumeti ako ga iskopirate u prazan radni list.

Kako se kopira primer

U ovom primeru se pretražuje kolona „ID stavke“ tabele proizvoda za bebe i upoređuju vrednosti u kolonama „Cena“ i „Marža“ da bi se izračunala cena i isprobali uslovi.

1 2 3 4 5 6

A B C D ID stavke Stavka Cena Marža ST-340 Kolica 14.567 din. 30% BI-567 Portikla 356 din. 40% DI-328 Pelene 2.145 din. 35% WI-989 Vlažne maramice 512 din. 40% AS-469 Aspirator 256 din. 45% Formula Opis (rezultat) = VLOOKUP("DI-328", A2:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 4, FALSE)) Izračunava maloprodajnu cenu pelena dodavanjem procenta marže na cenu. (2.896 din.) = (VLOOKUP("WI-989", A2:D6, 3, FALSE) * (1 + VLOOKUP("WI-989", A2:D6, 4, FALSE))) * (1 - 20%) Izračunava prodajnu cenu vlažnih maramica deljenjem maloprodajne cene sa određenim popustom. (573 din.) = IF(VLOOKUP(A2, A2:D6, 3, FALSE) >= 20, "Marža je " & 100 * VLOOKUP(A2, A2:D6, 4, FALSE) &"%", "Cena je manja od 2.000 din.") Ako je cena stavke veća ili jednaka iznosu od 2.000 din., prikazuje maržu; u protivnom, prikazuje cenu. (marža iznosi 30%) = IF(VLOOKUP(A3, A2:D6, 3, FALSE) >= 20, "Marža je: " & 100 * VLOOKUP(A3, A2:D6, 4, FALSE) &"%", "Cena je din." & VLOOKUP(A3, A2:D6, 3, FALSE)) Ako je cena stavke veća ili jednaka iznosu od 2.000 din., prikazuje maržu; u protivnom, prikazuje cenu. (cena iznosi 356 din.)

Primer 3

Primer ćete lakše razumeti ako ga iskopirate u prazan radni list.

Kako se kopira primer

U ovom primeru se pretražuje kolona „ID“ tabele radnika i upoređuju vrednosti u drugim kolonama, da bi se izračunala starosna dob i proverili uslovi za grešku.

1 2 3 4 5 6 7

A B C D E ID Prezime Ime Zvanje Datum rođenja 1 Stanković Nena Prodavac 08.12.1968. 2 Lazić Andrej Zamenik direktora prodaje 19.02.1952. 3 Lukić Jasna Prodavac 30.08.1963. 4 Petrović Milan Prodavac 19.09.1958. 5 Petrović Stevan Menadžer u prodaji 04.03.1955. 6 Jovanović Mihajlo Prodavac 02.07.1963. Formula Opis (rezultat) =INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1)) Za fiskalnu 2004. godinu, pronalazi starosnu dob radnika čiji je ID broj 5. Koristi funkciju YEARFRAC za deljenje datuma rođenja sa poslednjim datumom fiskalne godine i prikazuje rezultat kao ceo broj korišćenjem funkcije INT. (49) =IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "Radnik nije pronađen", VLOOKUP(5,A2:E7,2,FALSE)) Ako postoji radnik čiji je ID broj 5, prikazuje prezime radnika; u protivnom, prikazuje poruku „Radnik nije pronađen“. (Petrović) Funkcija ISNA daje vrednost TRUE, a funkcija VLOOKUP daje grešku #NA. =IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "Radnik nije pronađen", VLOOKUP(15,A3:E8,2,FALSE)) Ako postoji radnik čiji je ID broj 15, prikazuje prezime radnika; u protivnom, prikazuje poruku „Radnik nije pronađen“. (Radnik nije pronađen) Funkcija ISNA daje vrednost TRUE, a funkcija VLOOKUP daje grešku #NA. =VLOOKUP(4,A2:E7,3,FALSE) & " " & VLOOKUP(4,A2:E7,2,FALSE) & " is a " & VLOOKUP(4,A2:E7,4,FALSE) & "." Za radnika sa ID brojem 4, spaja vrednosti iz tri ćelije u kompletnu rečenicu. (Milan Petrović je prodavac.)

 Napomena   Prva formula u primeru iznad koristi YEARFRAC funkciju. Ukoliko ova funkcija nije raspoloživa i prikazuje grešku #NAME?, instalirajte i učitajte programski dodatak Analysis ToolPak.

Kako to možete da uradite?