U ovoj se temi opisuju najčešći razlozi funkcije VLOOKUP za pogrešan rezultat funkcije i prijedlozi za korištenje funkcija INDEX i MATCH .
Savjet: Osim toga, pogledajte karticu za brzi pregled: savjeti za otklanjanje poteškoća s funkcijom VLOOKUP koji predstavljaju uobičajene razloge #NA problema u praktičnoj PDF datoteci. PDF možete zajednički koristiti s drugima ili ispisati za vlastitu referencu.
Problem: vrijednost pretraživanja nije u prvom stupcu u argumentu table_array vrijednosti
Jedno ograničenje funkcije VLOOKUP jest to da može tražiti samo vrijednosti u lijevom stupcu u polju tablice. Ako se vrijednost pretraživanja ne nalazi u prvom stupcu polja, prikazat će se pogreška #N/A.
U sljedećoj tablici želimo dohvatiti broj prodanih jedinica za Kale.
Rezultati #N/A jer se vrijednost pretraživanja "Kale" pojavljuje u drugom stupcu (Voće i povrće) argumenta table_array A2:C10. U tom slučaju Excel ga traži u stupcu A, a ne u stupcu B.
Rješenje: to možete pokušati riješiti prilagodbom funkcije VLOOKUP tako da upućuje na 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 rezultati drugih izračuna ili možda postoje drugi logički razlozi zašto jednostavno ne možete premještati stupce. Rješenje je korištenje kombinacije funkcija INDEX i MATCH, koje mogu potražiti vrijednost u stupcu bez obzira na mjesto u tablici s vrijednostima. Pogledajte sljedeći odjeljak.
Razmislite o korištenju funkcije INDEX/MATCH
INDEX i MATCH dobre su mogućnosti za mnoge slučajeve u kojima VLOOKUP ne odgovara vašim potrebama. Ključna prednost funkcije INDEX/MATCH jest to što možete potražiti vrijednost u stupcu na bilo kojem mjestu u tablici s vrijednostima. INDEX vraća vrijednost iz navedene tablice/raspona prema njezinu položaju. MATCH vraća relativni položaj vrijednosti u tablici/rasponu. Koristite funkcije INDEX i MATCH zajedno u formuli da biste potražili vrijednost u tablici/polju navođenjem relativnog položaja vrijednosti u tablici/polju.
Postoji nekoliko prednosti korištenja funkcije INDEX/MATCH umjesto funkcije VLOOKUP:
-
Uz funkcije 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. Zašto je to važno? Pomoću funkcije VLOOKUP morate znati broj stupca koji sadrži povratnu vrijednost. Iako se to možda ne čini izazovnim, ponekad može biti nezgodno kada imate veliku tablicu i morate prebrojati broj stupaca. Osim toga, ako dodate/uklonite stupac u tablici, morate ponovno brojanje i ažurirati col_index_num argument. Kod funkcija INDEX i MATCH nije potrebno brojanje jer se stupac pretraživanja razlikuje od stupca koji ima povratnu vrijednost.
-
Pomoću funkcije INDEX i MATCH možete odrediti redak ili stupac u polju ili navesti oba. To znači da vrijednosti možete potražiti okomito i vodoravno.
-
FUNKCIJE INDEX i MATCH mogu se koristiti za traženje vrijednosti u bilo kojem stupcu. Za razliku od funkcije VLOOKUP , u kojoj možete potraž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 izrade dinamične reference na stupac koji sadrži povratnu vrijednost. To znači da u tablicu možete dodavati stupce bez prekidanja funkcije INDEX i MATCH. 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 podudaranja. 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 vrijednosti (po zadanom) ili točne vrijednosti. 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 izgradili sintaksu za INDEX/MATCH, morate koristiti argument polje/referencu iz funkcije INDEX i ugnijezdite sintaksu MATCH unutar njega. 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, koji se nalazi negdje u polju B2:B10, u kojem je povratna vrijednost prva vrijednost koja odgovara kelju))
Formula traži prvu vrijednost u ćeliji C2:C10 koja odgovara ćeliji Kale (u ćeliji B7) i vraća vrijednost u ćeliji C7 (100), što je prva vrijednost koja odgovara ćeliji Kale.
Problem: točno podudaranje nije pronađeno
Kada je range_lookup 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 u proračunskoj tablici postoje relevantni podaci, a VLOOKUP ih ne prima, provjerite nemaju li referentne ćelije skrivene razmake ili znakove koji se ne ispisuju. Osim toga, provjerite slijede li ćelije ispravnu 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 range_lookup vrijednost postavljena na TRUE , a vrijednost pretraživanja manja je od najmanje vrijednosti u polju, prikazat će se pogreška #N/A. TRUE traži približnu vrijednost 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 ćelija B2:C10 nema vrijednosti koje su manje od 100; stoga je došlo do pogreške.
Rješenje:
-
Po potrebi ispravite traženu vrijednost.
-
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 odjeljak u prethodnom odjeljku ovog članka. Pomoću funkcije INDEX/MATCH možete potražiti vrijednosti veće od, manje od ili jednake traženoj vrijednosti. Dodatne informacije o korištenju funkcije INDEX/MATCH umjesto funkcije VLOOKUP potražite u prethodnom odjeljku ove teme.
Problem: stupac pretraživanja nije sortiran uzlaznim redoslijedom
Ako je argument range_lookup postavljen na TRUE , a jedan od stupaca pretraživanja nije sortiran uzlaznim redoslijedom (A- Z), prikazat će se pogreška #N/A.
Rješenje:
-
Promijenite funkciju VLOOKUP da biste potražili točno podudaranje. Da biste to postavili, postavite range_lookup na FALSE. Za FALSE nije potrebno sortiranje.
-
Pomoću funkcije INDEX/MATCH potražite vrijednost u nesortiranoj tablici.
Problem: vrijednost je veliki broj s pomičnim zarezom
Ako u ćelijama imate vremenske vrijednosti ili velike decimalne brojeve, Excel vraća pogrešku #N/A zbog preciznosti s pomičnim zarezom. Brojevi s pomičnim zarezom brojevi su koji slijede nakon decimalnog zareza. (Excel vrijednosti vremena pohranjuje kao brojeve s pomičnim zarezom.) Excel ne može pohraniti brojeve s vrlo velikim plutajućim točkama, pa će se zaokružiti na 5 decimalnih mjesta da bi funkcija ispravno funkcionirala.
Rješenje: skratite brojeve zaokružujući ih na najviše pet decimalnih mjesta pomoću funkcije ROUND .
Treba li vam dodatna pomoć?
Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.
