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

Pretpostavimo da imate popis s brojevima lokacija ureda i morate saznati koji se zaposlenici nalaze u kojem uredu. Proračunska je tablica golema pa ćete možda pomisliti da je to izazovan zadatak. To je zapravo prilično jednostavno učiniti pomoću funkcije pretraživanja.

Funkcije VLOOKUP i HLOOKUP , zajedno s funkcijama INDEX i MATCH, neke su od najkorisnijih funkcija u programu Excel.

Evo primjera korištenja funkcije VLOOKUP.

=VLOOKUP(B2;C2:E7;3;TRUE)

U ovom je primjeru B2 prvi argument – element podataka koji je potreban funkciji za funkcioniranje. Za VLOOKUP taj je prvi argument vrijednost koju želite pronaći. Taj argument može biti referenca ćelije ili fiksna vrijednost, npr. "makovac" ili 21 000. Drugi je argument raspon ćelija, C2-:E7, u kojem se traži vrijednost koju želite pronaći. Treći je argument stupac u tom rasponu ćelija koji sadrži traženu vrijednost.

Četvrti argument nije obavezan. Unesite TRUE ili FALSE. Ako unesete TRUE ili izostavite taj argument, funkcija će vratiti vrijednost koja je približna prvom argumentu. Ako unesete FALSE, funkcija će se podudarati s vrijednošću navedenom u prvom argumentu. Drugim riječima, ako izostavite četvrti argument ili unesete TRUE, postići ćete veću fleksibilnost.

U ovom se primjeru prikazuje funkcioniranje funkcije. Kad u ćeliju B2 (prvi argument) unesete vrijednost, VLOOKUP pretražuje ćelije u rasponu C2:E7 (2. argument) te vraća najbližu podudaranost iz trećeg stupca u rasponu, stupca E (3. argumenta).

Četvrti je argument prazan, pa funkcija vraća približno podudaranje. Da ga nismo izostavili, u stupce C ili D morali bismo unijeti neku od vrijednosti da bismo uopće dobili neki rezultat.

Kad se upoznate s funkcijom VLOOKUP, jednako je jednostavno koristiti i funkciju HLOOKUP. Unosite jednake argumente, ali se pretražuju reci, a ne stupci.

Korištenje funkcija INDEX i MATCH umjesto funkcija VLOOKUP

Funkcija VLOOKUP ima određena ograničenja – funkcija VLOOKUP može dohvatiti vrijednost samo slijeva nadesno. To znači da bi se stupac koji sadrži vrijednost koju pretražujete uvijek trebao nalaziti lijevo od stupca u kojem se nalazi povratna vrijednost. Ako proračunska tablica nije izgrađena na taj način, nemojte koristiti VLOOKUP. Umjesto toga koristite kombinaciju funkcija INDEX i MATCH.

U ovom se primjeru prikazuje mali popis na kojem vrijednost za pretraživanje, Rijeka, nije u krajnjem lijevom stupcu. Stoga ne možemo koristiti VLOOKUP. Umjesto nje ćemo pomoću funkcije MATCH pronaći Rijeku u rasponu B1:B11. Nalazi se u retku 4. Zatim funkcija INDEX tu vrijednost koristi kao argument pretraživanja pa u 4. retku (u stupcu D) pronalazi broj stanovnika Rijeke. Formula koju ste koristili prikazana je u ćeliji A14.

Isprobajte sami

Ako želite eksperimentirati s funkcijama za dohvaćanje prije nego što ih primijenite na vlastite podatke, evo oglednih podataka.

VLOOKUP Example at work

Kopirajte sljedeće podatke u praznu proračunsku tablicu.

Gustoća	Viskoznost	Temperatura
0,457	3,55	500
0,525	3,25	400
0,606	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)	Pomoću približnog podudaranja traži vrijednost 1 u stupcu A, pronalazi sljedeću najveću vrijednost manju od 1 ili jednaku 1 u stupcu A, koja je 0,946, a zatim vraća vrijednost iz stupca B u istom retku.	2,17
=VLOOKUP(1;A2:C10;3;TRUE)	Pomoću približnog podudaranja traži vrijednost 1 u stupcu A, pronalazi sljedeću najveću vrijednost manju od 1 ili jednaku 1 u stupcu A, koja je 0,946, a zatim vraća vrijednost iz stupca C u istom retku.	100
=VLOOKUP(0,7;A2:C10;3;FALSE)	Pomoću točnog podudaranja traži vrijednosti 0,7 u stupcu A. Budući da u stupcu A ne postoji vrijednost točnog podudaranja, vraća se pogreška.	#N/A
=VLOOKUP(0,1;A2:C10;2;TRUE)	Pomoću približnog podudaranja traži vrijednosti 0,1 u stupcu A. Budući da je 0,1 manje od najmanje vrijednosti u stupcu A, vraća se pogreška.	#N/A
=VLOOKUP(2;A2:C10;2;TRUE)	Pomoću približnog podudaranja traži vrijednost 2 u stupcu A, pronalazi sljedeću najveću vrijednost manju od 2 ili jednaku 2 u stupcu A, koja je 1,29, a zatim vraća vrijednost iz stupca B u istom retku.	1,71

HLOOKUP primjer

Kopirajte sve ćelije iz ove tablice i zalijepite ih u ćeliju A1 na praznom radnom listu programa Excel.

Osovine	Ležajevi	Vijci
4	4	9
5	7	10
6	8	11
Formula	Opis	Rezultat
=HLOOKUP("Osovine"; A1:C4; 2; TRUE)	Traži riječ "Osovine" u retku 1 i vraća vrijednost iz retka 2 koji se nalazi u istom stupcu (stupac A).	4
=HLOOKUP("Ležajevi"; A1:C4; 3; FALSE)	Traži riječ "Ležajevi" u retku 1 i vraća vrijednost iz retka 3 koji se nalazi u istom stupcu (stupac B).	7
=HLOOKUP("B"; A1:C4; 3; TRUE)	Traži "B" u retku 1 i vraća vrijednost iz retka 3 koji se nalazi u istom stupcu. S obzirom na to da ne postoji "B", koristi se najveća vrijednost u retku 1 koja je manja od "B": "Osovine" u stupcu A.	5
=HLOOKUP("Vijci"; A1:C4; 4)	Traži riječ "Vijci" u retku 1 i vraća vrijednost iz retka 4 koji se nalazi u istom stupcu (stupac C).	11
=HLOOKUP(3;{1;2;3|"a";"b";"c"|"d";"e";"f"};2;TRUE)	Traži broj 3 u konstanti polja s tri retka i vraća vrijednost iz retka 2 u istom (u ovom slučaju, trećem) stupcu. U konstanti polja postoje tri retka vrijednosti, svaki redak odijeljen je ravnom crtom (|). S obzirom na to da se "c" nalazi u retku 2 i u istom stupcu kao i 3, vraća se "c".	c

Primjeri funkcija INDEX i MATCH

U posljednjem se primjeru koristi kombinacija funkcija INDEX i MATCH radi dohvaćanja najstarijeg broja fakture i odgovarajućeg datuma za svaki od pet gradova. Budući da se datum vraća kao broj, kao datum ga oblikujemo pomoću funkcije TEXT. U funkciji INDEX zapravo se kao argument koristi rezultat funkcije MATCH. U svakoj se formuli dvaput koristi kombinacija funkcija INDEX i MATCH – najprije za dohvaćanje broja fakture, a zatim za dohvaćanje datuma.

Kopirajte sve ćelije iz ove tablice i zalijepite ih u ćeliju A1 na praznom radnom listu programa Excel.

Faktura	Grad	Datum fakture	Najstarija faktura po gradu uz datum
3115	Osijek	07.04.12.	="Osijek= "&INDEX($A$2:$C$33;MATCH("Osijek";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Osijek";$B$2:$B$33;0);3);"d. m. gg.")
3137	Osijek	09.04.12.	="Rijeka = "&INDEX($A$2:$C$33;MATCH("Rijeka";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Rijeka";$B$2:$B$33;0);3);"d. m. gg.")
3154	Osijek	11.04.12.	="Šibenik = "&INDEX($A$2:$C$33;MATCH("Šibenik";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Šibenik";$B$2:$B$33;0);3);"d. m. gg.")
3191	Osijek	21.04.12.	="Dubrovnik = "&INDEX($A$2:$C$33;MATCH("Dubrovnik";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Dubrovnik";$B$2:$B$33;0);3);"d. m. gg.")
3293	Osijek	25.04.12.	="Zagreb = "&INDEX($A$2:$C$33;MATCH("Zagreb";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Zagreb";$B$2:$B$33;0);3);"d. m. gg.")
3331	Osijek	27.04.12.
3350	Osijek	28.04.12.
3390	Osijek	01.05.12.
3441	Osijek	02.05.12.
3517	Osijek	08.05.12.
3124	Rijeka	09.04.12.
3155	Rijeka	11.04.12.
3177	Rijeka	19.04.12.
3357	Rijeka	28.04.12.
3492	Rijeka	06.05.12.
3316	Šibenik	25.04.12.
3346	Šibenik	28.04.12.
3372	Šibenik	01.05.12.
3414	Šibenik	01.05.12.
3451	Šibenik	02.05.12.
3467	Šibenik	02.05.12.
3474	Šibenik	04.05.12.
3490	Šibenik	05.05.12.
3503	Šibenik	08.05.12.
3151	Dubrovnik	09.04.12.
3438	Dubrovnik	02.05.12.
3471	Dubrovnik	04.05.12.
3160	Zagreb	18.04.12.
3328	Zagreb	26.04.12.
3368	Zagreb	29.04.12.
3420	Zagreb	01.05.12.
3501	Zagreb	06.05.12.

Dodatne informacije

Kartica za brzi pregled: podsjetnik za VLOOKUP

Funkcije za dohvaćanje podataka i reference (referenca)

Korištenje argumenta polje_tablica u funkciji VLOOKUP

Početak rada s programom Excel besplatno na webu