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

Pokušajte da koristite nove XLOOKUP i XMATCH funkcije, poboljšane verzije funkcija opisanih u ovom članku. Ove nove funkcije podrazumevano rade u bilo kom smeru i vraćaju tačna podudaranja, što ih čini lakšim i praktičnijim za korišćenje od prethodnika.

Pretpostavimo da imate listu brojeva lokacija kancelarije i treba da znate koji zaposleni se nalaze u svakoj kancelariji. Unakrsna tabela je ogromna, pa možete da mislite da je to izazovan zadatak. To je zapravo prilično lako uraditi sa funkcijom pronalaženja.

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

Funkcija čarobnjaka za pronalaženje više nije dostupna u programu Excel.

Evo primera korišćenja funkcije VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TAČNO)

U ovom primeru, B2 je prvi argument – element podataka koji je potreban funkciji da bi funkcionisala. Za funkciju VLOOKUP, ovaj prvi argument je vrednost koju želite da pronađete. Ovaj argument može biti referenca na ćeliju ili fiksna vrednost kao što je "simić" ili 21.000. Drugi argument je opseg ćelija C2-:E7 u kojem se traži vrednost koju želite da pronađete. Treći argument je kolona u tom opsegu ćelija koja sadrži vrednost koju tražite.

Četvrti argument je opcionalan. Unesite TRUE ili FALSE. Ako unesete vrednost „TAČNO“ ili ostavite argument prazan, funkcija vraća vrednost koja se približno poklapa sa vrednošću koju ste naveli u prvom argumentu. Ako unesete vrednost FALSE, funkcija će se podudarati sa vrednošću navedenom u prvom argumentu. Drugim rečima, ostavljanje četvrtog argumenta praznog – ili unosa TRUE – daje vam veću fleksibilnost.

Ovaj primer prikazuje kako funkcija radi. Kada unesete vrednost u ćeliju B2 (prvi argument), funkcija VLOOKUP pretražuje ćelije u opsegu C2:E7 (2. argument) i vraća najbliže približno podudaranje iz treće kolone u opsegu, kolone E (treći argument).

Tipična upotreba funkcije VLOOKUP

Četvrti argument je prazan, tako da funkcija vraća približno podudaranje. U suprotnom, morali biste da unesete neku od vrednosti u kolonama C ili D da biste uopšte dobili rezultat.

Kada se dobro osećate uz funkciju VLOOKUP, podjednako je laka za korišćenje funkcije HLOOKUP. Unose se isti argumenti, ali se pretražuju u redovima umesto u kolonama.

Korišćenje funkcija INDEX i MATCH umesto funkcije VLOOKUP

Postoje određena ograničenja korišćenja funkcije VLOOKUP – funkcija VLOOKUP može samo da potraži vrednost sleva nadesno. To znači da kolona koja sadrži vrednost koju tražite uvek treba da se nalazi sa leve strane kolone koja sadrži povratnu vrednost. Ako unakrsna tabela nije izgrađena na ovaj način, nemojte koristiti funkciju VLOOKUP. Umesto toga koristite kombinaciju funkcija INDEX i MATCH.

Ovaj primer pokazuje malu listu na kojoj se vrednost po kojoj želimo da pretražimo Čačak nalazi u krajnje levoj koloni. Dakle, ne možemo da koristimo VLOOKUP. Umesto toga koristimo funkciju MATCH da pronađemo Čikago u opsegu B1:B11. Naрemo ga u redu 4. Zatim funkcija INDEX koristi tu vrednost kao argument za pronalaženje i pronalazi populaciju za Čikago u 4. koloni (koloni D). Upotrebljena formula je prikazana u ćeliji A14.

Korišćenje funkcija INDEX i MATCH za traženje vrednosti

Za više primera korišćenja funkcija INDEX i MATCH umesto funkcije VLOOKUP, pogledajte članak https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ je Bil Jelen, Microsoft MVP.

Pokušaj

Ako želite da eksperimentišete sa funkcijama za pronalaženje pre nego što ih isprobate sa sopstvenim podacima, evo nekih uzoraka podataka.

VLOOKUP Primer na poslu

Kopirajte sledeće podatke u praznu unakrsnu tabelu.

Pre nego što nalepite podatke u Excel, podesite širinu kolona za kolone od A do C na 250 piksela i izaberite stavku Prelomi tekst (karticaPočetak, grupa Poravnavanje).

Gustina

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 vrednost 1 u koloni A, pronalazi najveću vrednost manju ili jednaku 1 u koloni A koja je 0,946, a zatim vraća vrednost iz kolone B u isti red.

2,17

=VLOOKUP(1,A2:C10,3,TAČNO)

Pomoću približnog podudaranja traži vrednost 1 u koloni A, pronalazi najveću vrednost manju ili jednaku 1 u koloni A koja je 0.946, a zatim vraća vrednost iz kolone C u isti red.

100

=VLOOKUP(0.7,A2:C10,3,NETAČNO)

Pomoću potpunog podudaranja pretražuje vrednost 0.7 u koloni A. Pošto ne postoji potpuno podudaranje u koloni A, dobija se greška.

#N/A

=VLOOKUP(0.1,A2:C10,2,TAČNO)

Pomoću približnog podudaranja pretražuje 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,TAČNO)

Pomoću približnog podudaranja traži vrednost 2 u koloni A, pronalazi najveću vrednost manju ili jednaku 2 u koloni A koja je 1,29, a zatim vraća vrednost iz kolone B u isti red.

1,71

HLOOKUP primer

Kopirajte sve ćelije iz ove tabele i nalepite ih u ćeliju A1 u praznoj radnoj svesci programa Excel.

Pre nego što nalepite podatke u Excel, podesite širinu kolona za kolone od A do C na 250 piksela i izaberite stavku Prelomi tekst (karticaPočetak, grupa Poravnavanje).

Osovine

Ležajevi

Zavrtnji

4

4

9

5

7

10

6

8

11

Formula

Opis

Rezultat

=HLOOKUP("Osovine", A1:C4, 2, TRUE)

Traži „Osovine“ u redu 1 i daje vrednost iz reda 2 koja je u istoj koloni (koloni A).

4

=HLOOKUP("Ležajevi", A1:C4, 3, FALSE)

Traži „Ležajevi“ u redu 1 i daje vrednost iz reda 3 koja je u istoj koloni (koloni B)..

7

=HLOOKUP("B", A1:C4, 3, TRUE)

Traži „B“ u redu 1 i daje vrednost iz reda 3 koja je u istoj koloni. Pošto nije pronađena tražena podudarnost, koristi se najveća vrednost u redu 1 koja je manja od „B“: „Osovine“, u koloni A.

5

=HLOOKUP("Bolts", A1:C4, 4)

Traži „Zavrtnji“ u redu 1 i daje vrednost iz reda 4 koja je u istoj koloni (koloni C).

11

=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)

Traži broj 3 u konstanti niza koja sadrži tri reda i daje vrednost iz reda 2 u istoj (u ovom slučaju trećoj) koloni. U konstanti niza nalaze se tri reda vrednosti, a svaki red odvojen je tačkom i zarezom (;). Pošto je „c“ pronađeno u redu 2 i u istoj koloni kao i 3, dobija se „c“.

c

Primeri INDEX i MATCH

Ovaj poslednji primer zajedno koristi funkcije INDEX i MATCH kako bi se vratio najraniji broj fakture i odgovarajući datum za svaki od pet gradova. Pošto se datum vraća kao broj, koristimo funkciju TEXT da bismo ga oblikovali kao datum. Funkcija INDEX zapravo koristi rezultat funkcije MATCH kao argument. Kombinacija funkcija INDEX i MATCH se dvaput koristi u svakoj formuli – prvi put da bi se dobio broj fakture, a drugi put da bi se dobio datum.

Kopirajte sve ćelije iz ove tabele i nalepite ih u ćeliju A1 u praznoj radnoj svesci programa Excel.

Pre nego što nalepite podatke u Excel, podesite širinu kolona za kolone od A do D na 250 piksela i izaberite stavku Prelomi tekst (karticaPočetak, grupa Poravnavanje).

Faktura

Grad

Datum fakture

Najranija faktura po gradovima sa datumom

3115

Aranđelovac

07.04.12.

="Aranđelovac = "&INDEX($A$2:$C$33,MATCH("Aranđelovac",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Aranđelovac",$B$2:$B$33,0),3),"m/d/yy")

3137

Aranđelovac

09.04.12.

="Apatin = "&INDEX($A$2:$C$33,MATCH("Apatin",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Apatin",$B$2:$B$33,0),3),"m/d/yy")

3154

Aranđelovac

11.04.12.

="Beograd = "&INDEX($A$2:$C$33,MATCH("Beograd",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Beograd",$B$2:$B$33,0),3),"m/d/yy")

3191

Aranđelovac

21.04.12.

="Novi Sad = "&INDEX($A$2:$C$33,MATCH("Novi Sad",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Novi Sad",$B$2:$B$33,0),3),"m/d/yy")

3293

Aranđelovac

25.04.12.

="Valjevo = "&INDEX($A$2:$C$33,MATCH("Valjevo",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Valjevo",$B$2:$B$33,0),3),"m/d/yy")

3331

Aranđelovac

27.04.12.

3350

Aranđelovac

28.04.12.

3390

Aranđelovac

01.05.12.

3441

Aranđelovac

02.05.12.

3517

Aranđelovac

08.05.12.

3124

Apatin

09.04.12.

3155

Apatin

11.04.12.

3177

Apatin

19.04.12.

3357

Apatin

28.04.12.

3492

Apatin

06.05.12.

3316

Beograd

25.04.12.

3346

Beograd

28.04.12.

3372

Beograd

01.05.12.

3414

Beograd

01.05.12.

3451

Beograd

02.05.12.

3467

Beograd

02.05.12.

3474

Beograd

04.05.12.

3490

Beograd

05.05.12.

3503

Beograd

08.05.12.

3151

Novi Sad

09.04.12.

3438

Novi Sad

02.05.12.

3471

Novi Sad

04.05.12.

3160

Valjevo

18.04.12.

3328

Valjevo

26.04.12.

3368

Valjevo

29.04.12.

3420

Valjevo

01.05.12.

3501

Valjevo

06.05.12.

funkcije VLOOKUP

(referenca)

argument table_array funkciji VLOOKUP

Da li vam je potrebna dodatna pomoć?

Želite još opcija?

Istražite pogodnosti pretplate, pregledajte kurseve za obuku, saznajte kako da obezbedite uređaj i još mnogo toga.

Zajednice vam pomažu da postavljate pitanja i odgovarate na pitanja, dajete povratne informacije i čujete mišljenje od stručnjaka sa bogatim znanjem.