U ovoj se temi opisuju najčešći razlozi funkcije VLOOKUP za pogrešan rezultat funkcije i prijedlozi za korištenje funkcije INDEX i MATCH.

Savjet: Pogledajte i karticu za brzi pregled: savjeti za otklanjanje poteškoća funkcije VLOOKUP koji predstavljaju uobičajene razloge #NA problema u praktičnoj PDF datoteci. PDF možete zajednički koristiti s drugim korisnicima ili ispisati za vlastitu referencu.

Problem: vrijednost pretraživanja ne nalazi se u prvom stupcu u argumentu table_array.

Jedno ograničenje funkcije VLOOKUP jest da može tražiti samo vrijednosti u lijevom stupcu u polju tablice. Ako se vrijednost pretraživanja ne nalazi u prvom stupcu polja, vidjet ćete pogrešku #N/A.

U sljedećoj tablici želimo dohvatiti broj prodanih jedinica za Kale.

#NA u funkciji VLOOKUP: vrijednost pretraživanja nije u prvom stupcu polja tablice

Rezultat #N/A jer se vrijednost pretraživanja "Kale" pojavljuje u drugom stupcu (Proizvodi) argumenta table_array A2:C10. U tom Excel traži u stupcu A, a ne u stupcu B.

Rješenje:to možete pokušati riješiti prilagodbom funkcije VLOOKUP tako da referencije odgovarajući stupac. Ako to nije moguće, pokušajte premještati stupce. To može biti vrlo nepraktično ako imate velike ili složene proračunske tablice u kojima su vrijednosti ćelija rezultat drugih izračuna – ili možda postoje drugi logički razlozi zbog kojih jednostavno ne možete premještati stupce. Rješenje je korištenje kombinacije funkcija INDEX i MATCH, koje mogu tražiti vrijednost u stupcu bez obzira na položaj mjesta u tablici pretraživanja. Pogledajte sljedeći odjeljak.

Razmislite o korištenju alata INDEX/MATCH

INDEX i MATCH dobre su mogućnosti za mnoge slučajeve u kojima VLOOKUP ne zadovoljava vaše potrebe. Ključna je prednost funkcije INDEX/MATCH to što možete pronaći vrijednost u stupcu na bilo kojem mjestu u tablici s vrijednostima. INDEX vraća vrijednost iz određene tablice/raspona – prema njezinu položaju. MATCH vraća relativni položaj vrijednosti u tablici/rasponu. Pomoću mogućnosti INDEX i MATCH u formuli potražite vrijednost u tablici/polju tako da navedete relativni položaj vrijednosti u tablici/polju.

Postoji nekoliko prednosti korištenja funkcije INDEX/MATCH umjesto funkcije VLOOKUP:

  • S funkcijama INDEX i MATCH povratna vrijednost ne mora biti u istom stupcu kao stupac s vrijednostima. To se razlikuje od funkcije VLOOKUP u kojem se povratna vrijednost mora nalaziti u navedenom rasponu. Kakve to veze ima? Pomoću funkcije VLOOKUP morate znati broj stupca koji sadrži povratnu vrijednost. Iako se to možda ne čini zahtjevnim, može biti zahtjevno kada imate veliku tablicu i morate brojati broj stupaca. Ako dodate/uklonite stupac u tablici, morate ponovno prebrojati i ažurirati argument col_index_num. Uz FUNKCIJE INDEX i MATCH brojanje nije potrebno jer se stupac pretraživanja razlikuje od stupca s povratnom vrijednošću.

  • Pomoću mogućnosti INDEX i MATCH možete odrediti redak ili stupac u polju ili navesti oba. To znači da vrijednosti možete tražiti okomito i vodoravno.

  • INDEX i MATCH mogu se koristiti za pretraživanje vrijednosti u bilo kojem stupcu. Za razliku od funkcije VLOOKUP – u kojoj možete tražiti samo vrijednost u prvom stupcu tablice – INDEX i MATCH funkcionirat će ako se vrijednost pretraživanja nalazi u prvom stupcu, zadnjem ili bilo gdje između.

  • INDEX i MATCH nude fleksibilnost dinamičke reference na stupac koji sadrži povratnu vrijednost. To znači da u tablicu možete dodavati stupce bez prekidanja indexa i matcha. S druge strane, VLOOKUP se lomi ako u tablicu morate dodati stupac jer stvara statičnu referencu na tablicu.

  • INDEX i MATCH nude veću fleksibilnost u podudarnosti. INDEX i MATCH mogu pronaći točno podudaranje ili vrijednost koja je veća ili manja od vrijednosti pretraživanja. VLOOKUP će tražiti samo najbliže podudaranje s vrijednošću (po zadanom) ili točnom vrijednošću. VLOOKUP po zadanom pretpostavlja i da je prvi stupac u polju tablice sortiran abecednim redom, a pretpostavimo da tablica nije postavljena na taj način, VLOOKUP će vratiti prvo najbliže podudaranje u tablici, što možda nisu podaci koje tražite.

Sintaksa

Da biste slagali sintaksu za INDEX/MATCH, morate koristiti argument polje/referenca iz funkcije INDEX i ugnijezditi sintaksu MATCH unutar funkcije. Ovo je obrazac:

=INDEX(polje ili referenca, MATCH(lookup_value,lookup_array,[match_type])

Pomoću funkcije INDEX/MATCH zamijenimo VLOOKUP iz gornjeg primjera. Sintaksa će izgledati ovako:

=INDEX(C2:C10;MATCH(B13;B2:B10;0))

Na jednostavnom engleskom to znači:

=INDEX(vrati vrijednost iz C2:C10, koja će MATCH(Kale, koja se nalazi negdje u polju B2:B10, u kojem je povratna vrijednost prva vrijednost koja odgovara kelju))

Funkcije INDEX i MATCH mogu se koristiti kao zamjena za VLOOKUP

Formula traži prvu vrijednost u ćeliji C2:C10 koja odgovara e-vrijednosti Kale (u ćeliji B7) i vraća vrijednost u ćeliji C7 (100), što je prva vrijednost koja odgovara kelju.

Problem: točno podudaranje nije pronađeno

Kada je range_lookup argument FALSE , a VLOOKUP ne može pronaći točno podudaranje u podacima , vraća pogrešku #N/A.

Rješenje:ako ste sigurni da relevantni podaci postoje u proračunskoj tablici, a VLOOKUP ih ne prima, potrebno je vremena da provjerite imaju li referentne ćelije skrivene razmake ili znakove koji se ne ispisuju. Osim toga, provjerite prate li ćelije odgovarajuću vrstu podataka. Ćelije s brojevima, primjerice, moraju biti oblikovane kao Broj, a ne Tekst.

Razmislite i o čišćenju podataka u ćelijama pomoću funkcije CLEAN ili TRIM.

Problem: vrijednost pretraživanja manja je od najmanje vrijednosti u polju

Ako je argument range_lookup postavljen na TRUE – a vrijednost pretraživanja manja je od najmanje vrijednosti u polju – vidjet ćete pogrešku #N/A. TRUE traži približno podudaranje u polju i vraća najbližu vrijednost manju od vrijednosti pretraživanja.

U sljedećem je primjeru vrijednost pretraživanja 100, ali u rasponu B2:C10 nema vrijednosti koje su manje od 100; stoga je pogreška.

Pogreška N/A u funkciji VLOOKUP kada je vrijednost pretraživanja manja od najmanje vrijednosti u polju

Rješenje:

  • Po potrebi ispravite vrijednost pretraživanja.

  • Ako ne možete promijeniti vrijednost pretraživanja i potrebna vam je veća fleksibilnost s podudarnim vrijednostima, razmislite o korištenju funkcije INDEX/MATCH umjesto funkcije VLOOKUP – pogledajte gornji odjeljak u ovom članku. Pomoću funkcije INDEX/MATCH možete tražiti vrijednosti veće od vrijednosti koje su manje od ili jednake vrijednosti pretraživanja. Dodatne informacije o korištenju funkcije INDEX/MATCH umjesto funkcije VLOOKUP potražite u prethodnom odjeljku u ovoj temi.

Problem: stupac s vrijednostima nije sortiran uzlaznim redoslijedom

Ako je argument range_lookup postavljen na TRUE , a jedan od stupaca pretraživanja nije sortiran uzlaznim (A-Z) redoslijedom – vidjet ćete pogrešku #N/A.

Rješenje:

  • Promijenite funkciju VLOOKUP da biste ga točno usporedili. Da biste toučiniti, postavite argument range_lookup FALSE . Za FALSE nije potrebno sortiranje.

  • Pomoću funkcije INDEX/MATCH potražite vrijednost u ne sortiranoj tablici.

Problem: vrijednost je veliki broj s pomičnim zategom

Ako u ćelijama imate vremenske vrijednosti ili velike decimalne brojeve, Excel vraća pogrešku #N/A zbog preciznosti plutajuće točke. Brojevi s pomičnim zarezom brojevi su koji slijede nakon decimalnog zareza. (Excel vrijednosti vremena pohranjuje kao brojeve s pomičnim točkama.) Excel ne može pohraniti brojeve s vrlo velikim plutajućim točkama, pa će se da bi funkcija pravilno funkcionirala, brojevi plutajućih točaka morati zaokružiti na 5 decimalnih mjesta.

Rješenje:skratite brojeve tako da ih zaokružite na pet decimalnih mjesta pomoću funkcije ROUND.

Je li vam potrebna dodatna pomoć?

Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednici za odgovore.

Dodatne informacije

Potrebna vam je dodatna pomoć?

Proširite svoje vještine
Istražite osposobljavanje
Prvi koristite nove značajke
Pridružite se Microsoft Office Insiderima

Jesu li vam ove informacije bile korisne?

Koliko ste zadovoljni jezičnom kvalitetom?
Što je utjecalo na vaše iskustvo?

Hvala vam na povratnim informacijama!

×