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

Savjet: Pokušajte koristiti nove funkcije Xlookup i xmatch , poboljšane verzije funkcija opisanih u ovom članku. Te nove funkcije funkcioniraju u bilo kojem smjeru i automatski vraćaju točne podudarnosti, što ih čini lakšim i prikladnije za korištenje od svojih prethodnika.

Pretpostavimo da imate popis brojeva mjesta sustava Office i da morate znati koji su zaposlenici u svakom uredu. Proračunska tablica je ogromna, pa možda mislite da je to zahtjevan zadatak. To je zapravo prilično jednostavan za napraviti s Lookup (funkcija).

Funkcije VLOOKUP i HLOOKUP , zajedno s indeksom i podudaranjem,neke su od najkorisnijih funkcija u programu Excel.

Napomena: Značajka čarobnjaka za traženje više nije dostupna u programu Excel.

Evo primjera korištenja funkcije VLOOKUP.

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

U ovom primjeru B2 je prvi argument– element podataka koji funkcija mora funkcionirati. Ovaj prvi argument za VLOOKUP predstavlja vrijednost koju želite pronaći. Ovaj argument može biti referenca na ćelije ili fiksna vrijednost, kao što je "Smith" ili 21.000. Drugi argument jest raspon ćelija, C2-: E7, u kojem se traži vrijednost koju želite pronaći. Treći argument jest stupac u 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 sadrži prvi argument. Drugim riječima, kada je četvrti argument prazan – ili unos TRUE – nudi vam više fleksibilnosti.

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, stupac E (treći argument).

Tipična upotreba funkcije VLOOKUP

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

Ako vam se sviđa VLOOKUP, funkcija HLOOKUP jednako je jednostavna za korištenje. Iste argumente unosite, ali on pretražuje u recima umjesto u stupcima.

Korištenje indeksa i PODUDARANJA umjesto funkcije VLOOKUP

Postoje određena ograničenja uz korištenje značajke VLOOKUP – funkcija VLOOKUP može potražiti samo vrijednost slijeva nadesno. To znači da stupac koji sadrži vrijednost koju tražite uvijek mora biti smješten na lijevoj strani stupca koji sadrži povratnu vrijednost. Sada ako proračunska tablica nije izgrađena na ovaj način, nemojte koristiti VLOOKUP. Umjesto toga koristite kombinaciju funkcija INDEX i MATCH.

U ovom se primjeru prikazuje mali popis na kojem vrijednost koju želimo pretraživati u Chicagu nije u krajnjem lijevom stupcu. Dakle, ne možemo koristiti VLOOKUP. Umjesto toga koristit ćemo funkciju MATCH da bismo pronašli Chicago u rasponu B1: B11. Nalazi se u retku 4. Zatim indeks koristi tu vrijednost kao argument pretraživanja i pronalazi populaciju za Chicago u četvrtom stupcu (stupac D). Korištena formula prikazana je u ćeliji

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

Dodatne primjere korištenja indeksa i PODUDARANJA umjesto funkcije VLOOKUP potražite u članku https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ Bill jelen, Microsoft MVP.

Isprobajte sami

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

Primjeri VLOOKUP na poslu

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

Savjet: Prije nego što zalijepite podatke u Excel, postavite širine stupaca za stupce od do C do 250 piksela, a zatim kliknite prelamanje teksta (karticaPolazno , 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

HLOOKUP primjer

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

Savjet: Prije nego što zalijepite podatke u Excel, postavite širine stupaca za stupce od do C do 250 piksela, a zatim kliknite prelamanje teksta (karticaPolazno , 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 indeksa i PODUDARANJA

Ovaj zadnji primjer zapošljava 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 oblikujte 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 nego što zalijepite podatke u Excel, postavite širine stupaca za stupce A do D do 250 piksela, a zatim kliknite prelamanje teksta (karticaPolazno , 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.

Dodatni sadržaji

Kartica za brzi pregled:

funkcije pretraživanja i referenci za VLOOKUP (referenca)

pomoću argumenta table_array u funkciji VLOOKUP

Potrebna vam je dodatna pomoć?

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

Jesu li vam ove informacije bile korisne?

×