Pronalaženje vrednosti pomoću funkcije VLOOKUP, INDEX ili MATCH

Savet: Pokušajte da koristite novu Xlookup funkcije , poboljšane verzije funkcija opisanih u ovom članku. Ove nove funkcije funkcionišu u bilo kom pravcu i podrazumevano im daju tačne podudaranja, što ih čini lakšim i prikladnijim za korišćenje od svojih prethodnika.

Pretpostavimo da imate listu brojeva Office lokacija i treba da znate koji zaposleni se nalaze u svakom od njih. Unakrsna tabela je ogromna, pa možda mislite da je to izazovno zadatak. To se u stvari lako radi sa funkcijom pronalaženja.

Funkcije vanlookup i HLOOKUP , zajedno sa indeksom i podudarnosti,su neke od najkorisnijih funkcija u programu Excel.

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

Evo primera kako da koristite funkcija vanlookup.

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

U ovom primeru, B2 je prvi argument– element podataka koji funkcija treba da radi. Za VANLOOKUP, ovaj prvi argument je vrednost koju želite da pronađete. Ovaj argument može da bude referenca na ćeliju ili Fiksna vrednost kao što je "Savić" ili 21.000. Drugi argument je opseg ćelija, C2 –: E7, u kojoj se traži vrednost koju želite da pronađete. Treći argument je kolona u tom opsegu ćelija koje sadrže 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 FALSE, funkcija će se podudarati sa vrednošću koju pruža prvi argument. Drugim rečima, ostavljajući četvrti argument prazno – ili unosite TRUE – to vam daje veću fleksibilnost.

Ovaj primer prikazuje kako funkcija radi. Kada unesete vrednost u ćeliju B2 (prvi argument), VLOOKUP pretražuje ćelije u opsegu C2: E7 (drugi argument) i daje najbližu približno podudaranje iz treće kolone u opsegu, koloni 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 ste zadovoljni sa programom VANLOOKUP, funkcija HLOOKUP se jednako lako koristi. Unosite iste argumente, ali on pretražuje redove umesto kolona.

Korišćenje INDEKSA i podudaranja umesto funkcije VANLOOKUP

Postoje određena ograničenja sa korišćenjem usluge VILOOKUP – funkcija VILOOKUP može da traži samo vrednost sleva nadesno. To znači da kolona koja sadrži vrednost koju ste pronašli treba uvek da se nalazi sa leve strane kolone koja sadrži povratnu vrednost. Ako unakrsna tabela nije napravljena na ovaj način, ne koristite VILOOKUP. Umesto toga koristite kombinaciju funkcija INDEX i MATCH.

Ovaj primer prikazuje malu listu u kojoj se u Chicagu žele tražiti, nije u krajnjoj levoj koloni. Ne možemo da koristimo funkcija vanlookup. Umesto toga, koristite funkciju MATCH da biste pronašli Čikago B1: B11. Nalazi se u 4. Zatim, INDEX koristi tu vrednost kao argument za pronalaženje i pronalazi populaciju za Čikago u иetvrtoj koloni (kolona D). Formula koja se koristi prikazuje se u ćeliji a14.

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

Više primera korišćenja INDEKSA i podudaranja umesto funkcije VLOOKUP potražite u članku https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-INDEX-MATCH/ po članku jelena, Microsoft MVP.

Pokušaj

Ako želite da eksperimentišete sa funkcijama LOOKUP pre nego što ih isprobate sa sopstvenim podacima, evo nekoliko uzoraka podataka.

Funkcija vanlookup na poslu

Kopirajte sledeće podatke u praznu unakrsnu tabelu.

Savet: Pre nego što nalepite podatke u Excel, Podesi širinu kolone od A do C do 250 piksela i kliknite na dugme prelomi tekst (karticaPočetak , grupa poravnavanja ).

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

Primer HLOOKUP

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

Savet: Pre nego što nalepite podatke u Excel, Podesi širinu kolone od A do C do 250 piksela i kliknite na dugme prelomi tekst (karticaPočetak , grupa poravnavanja ).

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 INDEKSA i podudaranja

Ovaj poslednji primer koristi funkcije INDEX i MATCH zajedno da biste vratili najraniji broj fakture i odgovarajući datum za svaki od pet gradova. Pošto je datum vraćen 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.

Savet: Pre nego što nalepite podatke u Excel, Podesi širinu kolone od A do D do 250 piksela i kliknite na dugme prelomi tekst (karticaPočetak , grupa poravnavanja ).

Faktura

Grad

Datum fakture

Najranija faktura po gradovima sa datumom

3115

Atlanta

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

Atlanta

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

Atlanta

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

Atlanta

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

Atlanta

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

Atlanta

27.04.12.

3350

Atlanta

28.04.12.

3390

Atlanta

01.05.12.

3441

Atlanta

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

Dalas

25.04.12.

3346

Dalas

28.04.12.

3372

Beograd

01.05.12.

3414

Beograd

01.05.12.

3451

Dalas

02.05.12.

3467

Dalas

02.05.12.

3474

Dalas

04.05.12.

3490

Dalas

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.

Takođe pogledajte

Kartica sa kratkim karticama: vanfunkcija

Pronalaženje i referenca za pregled (referenca)

koristite argument table_array u funkciji vilookup

Potrebna vam je dodatna pomoć?

Razvijte Office veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Office Insider korisnicima

Da li su vam ove informacije koristile?

Hvala vam na povratnim informacijama!

Hvala za povratne informacije! Izgleda da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×