Ova tema opisuje najčešće VLOOKUP razloge za greške u funkciji i pruža predloge za korišćenje funkcija INDEX i MATCH umesto toga.
Savet: Takođe, pogledajte karticu sa kratkim uputstvima: Saveti za rešavanje problema u funkciji VLOOKUP koji pružaju uobičajene razloge za #NA problema u prikladnoj PDF datoteci. PDF možete da podelite sa drugim osobama ili da ga odštampate za svoje potrebe.
Problem: Vrednost za pronalaženje nije u prvoj koloni u argumentu table_array
Jedno ograničenje funkcije VLOOKUP je da može da potraži samo vrednosti u koloni sa najviše leve strane u nizu tabele. Ako vrednost za pronalaženje nije u prvoj koloni niza, videćete grešku #N/A.
U sledećoj tabeli, želimo da preuzmemo broj prodatih jedinica za kelj.
Do #N/A dolazi zato što se vrednost za pronalaženje "Kelj" pojavljuje u drugoj koloni (Proizvod) argumenta table_array A2:C10. U ovom slučaju, Excel ga traži u koloni A, a ne u koloni B.
Rešenje: Možete pokušati da ovo popravite tako što ćete podesiti vaš VLOOKUP da upućuje na odgovarajuću kolonu. Ako to nije moguće, onda pokušajte da premestite kolone. To može da bude veoma nepročitano i ako imate velike ili složene unakrsne tabele u kojima su vrednosti ćelija rezultati drugih izračunavanja – ili možda postoje drugi logički razlozi zašto jednostavno ne možete da premeštate kolone. Rešenje je da koristite kombinaciju funkcija INDEX i MATCH koje mogu da potraže vrednost u koloni bez obzira na njen položaj lokacije u tabeli za pronalaženje. Pogledajte sledeći odeljak.
Razmotrite korišćenje funkcije INDEX/MATCH umesto toga
FUNKCIJE INDEX i MATCH su dobre opcije za mnoge slučajeve u kojima VLOOKUP ne odgovara vašim potrebama. Ključna prednost funkcije INDEX/MATCH jeste to što možete da potražite vrednost u koloni na bilo kojoj lokaciji u tabeli za pronalaženje. INDEX daje vrednost iz navedene tabele/opsega – u skladu sa njenim položajem. MATCH daje relativni položaj vrednosti u tabeli/opsegu. Koristite funkcije INDEX i MATCH zajedno u formuli da biste potražili vrednost u tabeli/nizu tako što ćete navesti relativni položaj vrednosti u tabeli/nizu.
Postoji nekoliko prednosti korišćenja funkcija INDEX/MATCH umesto funkcije VLOOKUP:
-
Kod funkcija INDEX i MATCH, povratna vrednost ne mora da bude u istoj koloni kao kolona za pronalaženje. On se razlikuje od funkcije VLOOKUP, u kojoj povratna vrednost mora da bude u navedenom opsegu. Zašto je to važno? Kod funkcije VLOOKUP, morate da znate broj kolone koja sadrži povratnu vrednost. Iako ovo možda neće izgledati izazovno, to može da bude neuostavno kada imate veliku tabelu i morate da prebrojite kolone. Takođe, ako dodate/uklonite kolonu u tabeli, morate ponovo da prebrojite i ažurirate col_index_num argument. Kod funkcija INDEX i MATCH, nije potrebno brojanje, pošto se kolona za pronalaženje razlikuje od kolone koja sadrži povratnu vrednost.
-
Pomoću funkcija INDEX i MATCH možete da navedete red ili kolonu u nizu ili da navedete oba. To znači da vrednosti možete da pronalazite i vertikalno i horizontalno.
-
INDEX i MATCH mogu se koristiti za pronalaženje vrednosti u bilo kojoj koloni. Za razliku od funkcije VLOOKUP – u kojoj možete da potražite vrednost samo u prvoj koloni u tabeli – funkcije INDEX i MATCH će funkcionisati ako je vrednost za pronalaženje u prvoj koloni, poslednjoj ili bilo kojoj između njih.
-
INDEX i MATCH nude fleksibilnost pravljenja dinamičkih referenci na kolonu koja sadrži povratnu vrednost. To znači da možete da dodate kolone u tabelu bez prekidanja funkcija INDEX i MATCH. S druge strane, VLOOKUP se prekida ako treba da dodate kolonu u tabelu – pošto ona pravi statičku referencu na tabelu.
-
INDEX i MATCH nude veću fleksibilnost sa podudaranja. Funkcije INDEX i MATCH mogu da pronađu potpuno podudaranje ili vrednost koja je veća ili manja od vrednosti za pronalaženje. VLOOKUP će tražiti samo najbliže podudaranje vrednosti (podrazumevano) ili tačnu vrednost. VLOOKUP takođe podrazumevano pretpostavlja da je prva kolona u nizu tabele sortirana po abecednom redosledu, a ako pretpostavimo da vaša tabela nije podešena na taj način, VLOOKUP će vratiti prvo najsličnije podudaranje u tabeli, što možda nisu podaci koje tražite.
Sintaksa
Da biste napravili sintaksu za funkciju INDEX/MATCH, morate da koristite argument niza/reference iz funkcije INDEX i da ugnezdite MATCH sintaksu u njoj. Ovo uzmite obrazac:
=INDEX(niz ili referenca, MATCH(lookup_value,lookup_array,[match_type])
Hajde da koristimo funkciju INDEX/MATCH da bi zamenili VLOOKUP iz gorenavedenog primera. Sintaksa će izgledati ovako:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Na jednostavnom srpskom, to znači:
=INDEX(daje vrednost iz C2:C10, koja će MATCH(Kelj, koji se nalazi negde u nizu B2:B10, u kojem je povratna vrednost prva vrednost koja odgovara keljima))
Formula traži prvu vrednost u C2:C10 koja odgovara vrednosti Kelj (u B7) i daje vrednost u ćeliji C7 (100), što je prva vrednost koja se podudara sa vrednosti „kelj“.
Problem: Potpuno podudaranje nije pronađeno
Kada argument range_lookup FALSE, a VLOOKUP ne može da pronađe potpuno podudaranje u vašim podacima – daje grešku #N/A.
Rešenje: Ako ste sigurni da relevantni podaci postoje u unakrsnoj tabeli, a VLOOKUP ih ne hvata, potrebno je vreme da proverite da li ćelije na koje se upućuje nemaju skrivene razmake ili znakove koji neće biti odštampani. Takođe se uverite da ćelije prate ispravan tip podataka. Na primer, ćelije sa brojevima trebalo bi da budu oblikovane kao broj, a ne kao tekst.
Takođe, razmotrite korišćenje funkcije CLEAN ili TRIM za čišćenje podataka u ćelijama.
Problem: Vrednost za pronalaženje manja je od najmanje vrednosti u nizu
Ako je range_lookup vrednost "range_lookup TRUE" – a vrednost za pronalaženje je manja od najmanje vrednosti u nizu – videćete grešku #N/A. TRUE traži približno podudaranje u nizu i daje najbližu vrednost koja je manja od vrednosti za pronalaženje.
U sledećem primeru, vrednost za pronalaženje je 100, ali ne postoje vrednosti u opsegu B2:C10 koje su manje od 100; otuda i greška.
Rešenje:
-
Ispravite vrednost za pronalaženje po potrebi.
-
Ako ne možete da promenite vrednost za pronalaženje i potrebna vam je veća fleksibilnost sa podudarnim vrednostima, razmislite o korišćenju funkcija INDEX/MATCH umesto funkcije VLOOKUP – pogledajte gorenavedeni odeljak u ovom članku. Kod funkcija INDEX/MATCH, možete da pronađete vrednosti veće od, manje ili jednake vrednosti za pronalaženje. Više informacija o korišćenju funkcija INDEX/MATCH umesto funkcije VLOOKUP, potražite u prethodnom odeljku ove teme.
Problem: Kolona za pronalaženje nije sortirana po rastućem redosledu
Ako je range_lookup vrednost argumenta TRUE , a jedna od kolona za pronalaženje nije sortirana po rastućem (A-Z) redosledu – videćete grešku #N/A.
Rešenje:
-
Promenite funkciju VLOOKUP tako da traži tačno podudaranje. Da biste to uradili, podesite argument range_lookup na vrednost FALSE. Nije neophodno sortiranje za FALSE.
-
Upotrebite funkciju INDEX/MATCH za traženje vrednosti u nesortiranoj tabeli.
Problem: Vrednost je veliki broj pokretnog zareza
Ako u ćelijama imate vrednosti vremena ili velike decimalne brojeve, Excel daje grešku #N/A zbog preciznosti sa pokretnim zarezom. Brojevi sa pokretnim zarezom su brojevi koji slede posle decimalnog zareza. (Excel skladišti vrednosti vremena kao brojeve sa pokretnim zarezom.) Excel ne može da uskladišti brojeve sa veoma velikim plutajućim zarezima, tako da će brojevi plutajućih tačaka morati da budu zaokruženi na 5 decimalnih mesta da bi funkcija ispravno funkcionisala.
Rešenje: Skratite brojeve tako što ćete ih zaokružiti na najviše pet decimalnih mesta pomoću funkcije ROUND.
Potrebna vam je dodatna pomoć?
Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da potražite pomoć u Zajednicama.
