Ispravljanje pogreške #N/A u funkciji VLOOKUP

U ovoj se temi opisuju najčešći razlozi VLOOKUP za pogrešan rezultat u funkciji i nudi prijedloge za korištenje indeksa i podudaranja .

Savjet: Pogledajte i karticu za brzi pregled: Savjeti za otklanjanje poteškoća u programu VLOOKUP koje predstavljaju česte razloge #NA problema u prikladnu PDF datoteci. PDF možete zajednički koristiti s drugim korisnicima ili ih ispisati za vlastitu referencu.

Problem: vrijednost pretraživanja nije u prvom stupcu u argumentu table_array

Jedno ograničenje značajke VLOOKUP jest da može tražiti samo vrijednosti u lijevom stupcu tablice. Ako se vrijednost pretraživanja ne nalazi u prvom stupcu polja, prikazat će vam se pogreška #N/A.

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

#NA pogreška u programu VLOOKUP: vrijednost pretraživanja nije u prvom stupcu polja tablice

#N/A rezultira pogreškama jer se vrijednost pretraživanja "Kale" prikazuje u drugom stupcu (proizvodi) argumenta table_array a2: C10. U ovom slučaju Excel ga traži u stupcu A, A ne u stupcu B.

Rješenje: možete pokušati ispraviti to tako da PRILAGODITE VLOOKUP da biste referencirali ispravni stupac. Ako to nije moguće, pokušajte premjestiti stupce. To može biti i 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 i drugi logički razlozi zbog kojih jednostavno ne možete premještati stupce oko njih. Rješenje je korištenje kombinacije Funkcija INDEX i MATCH, koja može potražiti vrijednost u stupcu bez obzira na položaj lokacije u tablici za pretraživanje. Pogledajte sljedeći odjeljak.

Umjesto toga razmotrite korištenje indeksa/PODUDARANJA

Index i Match dobre su mogućnosti za mnoge slučajeve u kojima VLOOKUP ne zadovoljava vaše potrebe. Ključna prednost indeksa/PODUDARANJA jest da možete potražiti vrijednost u stupcu na bilo kojem mjestu u tablici za pretraživanje. INDEX vraća vrijednost iz određene tablice/raspona – prema njenom položaju. MATCH vraća relativni položaj vrijednosti u tablici/rasponu. Koristite indeks i UPARITE se u formuli da biste potražili vrijednost u tablici/polju prema određivanju relativnog položaja vrijednosti u tablici/polju.

Postoji nekoliko prednosti korištenja indeksa/PODUDARANJA umjesto VLOOKUP:

  • S INDEKSOM i PODUDARANJEM, povratna vrijednost ne mora biti u istom stupcu kao stupac za pretraživanje. To se razlikuje od funkcije VLOOKUP, u kojoj se Povratna vrijednost mora prikazivati u navedenom rasponu. Kako je to bitno? Ako koristite VLOOKUP, morate znati broj stupca koji sadrži povratnu vrijednost. Iako se to možda ne čini izazovnim, može biti težak kada imate veliku tablicu i morate prebrojati broj stupaca. Osim toga, ako dodate/uklonite stupac u tablici, morate ponovno prebrojati i ažurirati argument col_index_num . S INDEKSOM i PODUDARANJEM nema zahtjeva za brojanje jer se stupac za pretraživanje razlikuje od stupca koji sadrži povratnu vrijednost.

  • S INDEKSOM i PODUDARANJEM možete navesti redak ili stupac u polju – ili navesti oba. To znači da možete potražiti vrijednosti okomito i vodoravno.

  • INDEX i MATCH mogu se koristiti za traženje vrijednosti u bilo kojem stupcu. Za razliku od VLOOKUP – u kojem možete pogledati samo vrijednost u prvom stupcu tablice – indeks i podudaranje funkcionirat će ako se vrijednost vrijednosti nalazi u prvom stupcu, posljednjem ili bilo kojem mjestu između njih.

  • Index i Match nude fleksibilnost stvaranja dinamičke referenca na stupac koji sadrži povratnu vrijednost.To znači da u tablicu možete dodavati stupce bez prekida indeksa i PODUDARANJA. S druge strane, VLOOKUP prekida ako je potrebno dodati stupac u tablicu – budući da čini statički referencu na tablicu.

  • INDEX i MATCH nudi više fleksibilnosti s podudaranjem.INDEX i MATCH mogu pronaći točnu podudarnost 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 se po zadanom pretpostavlja da je prvi stupac u polju tablice sortiran abecednim redom, pa pretpostavimo da tablica nije postavljena na taj način, VLOOKUP će vratiti prvu najbližu podudarnost u tablici, što možda neće biti podaci koje tražite.

Sintaksa

Da biste izgradili sintaksu za indeks/podudaranje, morate koristiti argument Array/reference iz funkcije INDEX i ugnjezditi sintaksu PODUDARANJA unutar njega. Na taj se način prikazuje obrazac:

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

Iskoristimo indeks/podudaranje da bismo zamijenili VLOOKUP iz gornjeg primjera. Sintaksa će izgledati ovako:

= INDEKS (C2: C10, MATCH (B13; B2: I; 0))

U jednostavnom engleskom znači:

= Indeks (povratak vrijednosti iz C2: C10, koja će se uskladiti (Kale, koja se nalazi negdje u programu B2:, u kojoj je povratna vrijednost prva vrijednost koja odgovara Kale))

Funkcije indeksa i PODUDARANJA mogu se koristiti kao zamjena za VLOOKUP

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

Problem: točno podudaranje nije pronađeno

Kada je argument RANGE_LOOKUP FALSE – i VLOOKUP ne može pronaći točnu podudarnost u podacima – vraća pogrešku #N/a.

Rješenje: Ako ste sigurni da relevantni podaci postoje u proračunskoj tablici i da ga VLOOKUP ne može uhvatiti, potrebno je vrijeme da biste potvrdili da ćelije nemaju skrivene razmake ili znakove koji se ne ispisuju. Pobrinite se i da ćelije slijede odgovarajuću vrstu podataka. Ćelije s brojevima, primjerice, moraju biti oblikovane kao broj, a ne tekst.

Razmotrite i korištenje funkcije Clean ili Trim da biste očistili podatke u ćelijama.

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

Ako je argument Range_lookup postavljen na True, a vrijednost za traženje manja je od najmanjeg vrijednosti u polju, prikazat će vam se pogreška #N/a. TRUE izgleda za približna podudarnost u polju i vraća najbližu vrijednost manju od vrijednosti pretraživanja.

U sljedećem primjeru vrijednost je vrijednosti 100, ali nema vrijednosti u rasponu B2: C10 koji je manji od 100; Otuda pogreška.

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

Rješenje:

  • Ako je potrebno, ispravite vrijednost pretraživanja.

  • Ako ne možete promijeniti vrijednost pretraživanja i trebati veću fleksibilnost s podudaranjem vrijednosti, razmotrite korištenje indeksa/PODUDARANJA umjesto funkcije VLOOKUP – pogledajte odjeljak iznad u ovom članku. S INDEKSOM/PODUDARANJEM možete potražiti vrijednosti veće od, manje do ili jednako vrijednosti pretraživanja. Dodatne informacije o korištenju indeksa/PODUDARANJA umjesto značajke VLOOKUP potražite u prethodnom odjeljku u ovoj temi.

Problem: stupac za pretraživanje nije sortiran uzlaznim redoslijedom.

Ako je argument Range_lookup postavljen na True, a jedan od stupaca pretraživanja nije sortiran u redoslijedu uzlazno (A-Z) – prikazat će vam se pogreška #N/a.

Rješenje:

  • Promijenite funkciju VLOOKUP da biste potražili točno podudaranje. Da biste to učinili, postavite argument Range_lookup na False. Nije potrebno sortiranje za FALSE.

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

Problem: vrijednost je veliki broj plutajućih točaka

Ako u ćelijama postoje vremenske vrijednosti ili veliki decimalni brojevi, Excel će vratiti pogrešku #N/A zbog preciznosti plutajuće točke. Brojevi s pomičnim točkama brojevi su koji slijede nakon decimalnog zareza. (Excel pohranjuje vremenske vrijednosti kao brojeve plutajućih točaka.) Excel ne može pohraniti brojeve s vrlo velikim pomičnim točkama da bi funkcija ispravno funkcionirala, brojevi pomičnim točkama morat će se zaokruživati na pet decimalnih mjesta.

Rješenje: Skratite brojeve tako da ih zaokružuju na pet decimalnih mjesta pomoću funkcije Round .

Imate pitanje o određenoj funkciji?

Objavite pitanje na forumu zajednice korisnika programa Excel

Doprinos poboljšanju programa Excel

Imate li prijedloge kako unaprijediti novu verziju programa Excel? Ako imate, pročitajte teme na stranici Excel User Voice.

Vidi također

Napomena:  Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Jesu li vam te informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku.​

Potrebna vam je dodatna pomoć?

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×