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

Savjet: Pokušajte koristiti nove funkcije XLOOKUP i XMATCH , poboljšane verzije funkcija opisanih u ovom članku. Te nove funkcije po zadanom funkcioniraju u bilo kojem smjeru i vraćaju točne podudaranja, što ih olakšava i praktičnije koristi od prethodnika.

Pretpostavimo da imate popis brojeva lokacija ureda i da morate znati koji se zaposlenici nalaze u svakom uredu. Proračunska tablica je ogromna, pa možda mislite da je to izazovan zadatak. To je zapravo vrlo lako učiniti s pretraživanjem funkcije.

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

Napomena: Značajka čarobnjaka za traženje vrijednosti više nije dostupna 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 funkcija mora funkcionirati. U funkciji VLOOKUP taj je prvi argument vrijednost koju želite pronaći. Taj argument može biti referenca ćelije ili fiksna vrijednost kao što je "smith" 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 vrijednost koju tražite.

Č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 koju navedite prvim argumentom. Drugim riječima, ostavljanje četvrtog argumenta praznim ili unosom TRUE daje vam veću fleksibilnost.

U ovom se primjeru prikazuje funkcioniranje funkcije. Kada unesete vrijednost u ćeliju B2 (prvi argument), VLOOKUP pretražuje ćelije u rasponu C2:E7 (drugi argument) i vraća najbliže približno podudaranje iz trećeg stupca u rasponu, stupcu E (3. argumentu).

Tipična upotreba funkcije VLOOKUP

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

Kada ste udobni s funkcijom VLOOKUP, funkcija HLOOKUP jednako je jednostavna za korištenje. Unosite iste argumente, ali pretražuje retke umjesto u stupce.

Korištenje funkcija INDEX i MATCH umjesto funkcije VLOOKUP

Postoje određena ograničenja korištenja funkcije VLOOKUP – funkcija VLOOKUP može tražiti samo vrijednost slijeva nadesno. To znači da se stupac koji sadrži vrijednost koju ste potražili uvijek trebao nalaziti s lijeve strane stupca koji sadrži povratnu vrijednost. Sada, ako proračunska tablica nije ugrađ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 se vrijednost koju želimo pretražiti, Chicago, ne nalazi u krajnjem lijevom stupcu. Stoga ne možemo koristiti VLOOKUP. Umjesto toga, upotrijebit ćemo funkciju MATCH da bismo pronašli Chicago u rasponu B1:B11. Pronađeno je u retku 4. Zatim INDEX tu vrijednost koristi kao argument pretraživanja i pronalazi populaciju za Chicago u 4. stupcu (stupac D). Korištena formula prikazana je u ćeliji A14.

Traženje vrijednosti pomoću kombinacije funkcija INDEX i MATCH

Dodatne primjere korištenja funkcija INDEX i MATCH umjesto funkcije VLOOKUP potražite u članku https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Bill Jelen, Microsoftov MVP.

Isprobajte sami

Ako želite eksperimentirati s funkcijama pretraživanja prije nego što ih isprobate s vlastitim podacima, evo nekih oglednih podataka.

Vlookup Primjer na poslu

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

Savjet: Prije lijepljenja podataka u Excel postavite širine stupaca od A do C na 250 piksela, a zatim kliknite Prelamanje teksta (kartica Polazno, grupa Poravnanje).

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

Primjer programa HLOOKUP

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

Savjet: Prije lijepljenja podataka u Excel postavite širine stupaca od A do C na 250 piksela, a zatim kliknite Prelamanje teksta (kartica Polazno, grupa Poravnanje).

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 funkcije INDEX i MATCH

U ovom se zadnjem primjeru zajedno koriste funkcije INDEX i MATCH da bi se vratio najstariji broj fakture i odgovarajući datum za svaki od pet gradova. Budući da se datum vraća kao broj, pomoću funkcije TEXT oblikujemo ga kao datum. 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.

Savjet: Prije lijepljenja podataka u Excel postavite širine stupaca od A do D na 250 piksela, a zatim kliknite Prelamanje teksta (kartica Polazno, grupa Poravnanje).

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.

Kartica s kratkim pregledom: funkcije pretraživanja

i reference za VLOOKUP (referenca)

Koristite table_array u funkciji VLOOKUP

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.