Kada koristite formulu VLOOKUP u Excelu, ponekad možete završiti s ružnom pogreškom #N/A. To se događa kada vaša formula ne može pronaći traženu vrijednost.
U ovom vodiču pokazat ću vam različite načine korištenja IFERROR -a s VLOOKUP -om za rješavanje ovih #N/A pogrešaka koje se pojavljuju na vašem radnom listu.
Korištenje kombinacije IFERROR -a s VLOOKUP -om omogućuje vam da pokažete nešto smisleno umjesto greške #N/A (ili bilo koju drugu grešku).
Prije nego što se upustimo u pojedinosti o korištenju ove kombinacije, idemo najprije na brzinu kroz funkciju IFERROR i vidjeti kako ona radi.
Objašnjena funkcija IFERROR
S funkcijom IFERROR možete odrediti što bi se trebalo dogoditi u slučaju da formula ili referenca ćelije vrati pogrešku.
Ovdje je sintaksa funkcije IFERROR.
= IFERROR (vrijednost, vrijednost_ako_pogreška)
- vrijednost - ovo je argument koji se provjerava radi pogreške. U većini slučajeva to je ili formula ili referenca ćelije. Kada koristite VLOOKUP s IFERROR -om, formula VLOOKUP bila bi ovaj argument.
- vrijednost_ako_pogreška - ovo je vrijednost koja se vraća ako postoji pogreška. Procijenjene su sljedeće vrste pogrešaka: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, i #NULL !.
Mogući uzroci VLOOKUP -a vraćanja greške #N/A
Funkcija VLOOKUP može vratiti grešku #N/A iz bilo kojeg od sljedećih razloga:
- Vrijednost traženja nije pronađena u nizu za pretraživanje.
- U vrijednosti traženja (ili u nizu tablice) postoji vodeći, zadnji ili dvostruki razmak.
- Došlo je do pravopisne pogreške u vrijednosti pretraživanja ili u vrijednostima u nizu za pretraživanje.
Sve ove uzroke pogrešaka možete riješiti kombinacijom IFERROR i VLOOKUP. Međutim, trebali biste pripaziti na uzroke #2 i #3 i ispraviti ih u izvornim podacima, umjesto da dopustite IFERROR -u da ih riješi.
Napomena: IFERROR bi tretirao pogrešku bez obzira na to što ju je uzrokovalo. Ako samo želite liječiti pogreške uzrokovane time što VLOOKUP ne može pronaći vrijednost pretraživanja, umjesto toga upotrijebite IFNA. To će osigurati da se pogreške osim #N/A ne tretiraju, a vi možete istražiti te druge pogreške.
Pomoću funkcije TRIM možete tretirati vodeće, zadnje i dvostruke razmake.
Zamjena greške VLOOKUP #N/A smislenim tekstom
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:
Kao što vidite da formula VLOOKUP vraća pogrešku jer vrijednost pretraživanja nije na popisu. Tražimo rezultat za Glen, koji nije na tablici rezultata.
Iako je ovo vrlo mali skup podataka, možda ćete dobiti ogromne skupove podataka u kojima morate provjeriti pojavu mnogih stavki. Za svaki slučaj kada vrijednost nije pronađena, dobit ćete pogrešku #N/A.
Evo formule koju možete koristiti da dobijete nešto smisleno umjesto greške #N/A.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Nije pronađeno")
Gornja formula vraća tekst "Nije pronađeno" umjesto greške #N/A. Istu formulu možete koristiti i za vraćanje praznog, nula ili bilo kojeg drugog smislenog teksta.
Ugniježđenje VLOOKUP -a s IFERROR funkcijom
U slučaju da koristite VLOOKUP, a tablica za pretraživanje je fragmentirana na istom radnom listu ili različitim radnim listovima, morate provjeriti vrijednost VLOOKUP kroz sve ove tablice.
Na primjer, u dolje prikazanom skupu podataka postoje dvije zasebne tablice imena učenika i rezultata.
Ako moram pronaći ocjenu Grace u ovom skupu podataka, moram upotrijebiti funkciju VLOOKUP za provjeru prve tablice, a ako vrijednost nije pronađena u njoj, provjerite drugu tablicu.
Evo ugniježđene IFERROR formule koju mogu koristiti za traženje vrijednosti:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
Korištenje VLOOKUP -a s IF -om i ISERROR -om (verzije prije Excela 2007)
IFERROR funkcija uvedena je u Excel 2007 za Windows i Excel 2016 u Mac.
Ako koristite prethodne verzije, funkcija IFERROR neće raditi u vašem sustavu.
Funkciju IFERROR možete replicirati kombinacijom IF funkcije i ISERROR funkcije.
Dopustite mi da vam brzo pokažem kako koristiti kombinaciju IF i ISERROR umjesto IFERROR.
U gornjem primjeru, umjesto IFERROR -a, možete koristiti i formulu prikazanu u ćeliji B3:
= IF (ISERROR (A3), ”Not Found”, A3)
Dio formule ISERROR provjerava ima li pogrešaka (uključujući pogrešku #N/A) i vraća TRUE ako je greška pronađena, a FALSE ako nije.
- Ako je TRUE (što znači da postoji pogreška), funkcija IF vraća navedenu vrijednost (u ovom slučaju "Nije pronađeno").
- Ako je FALSE (što znači da nema greške), funkcija IF vraća tu vrijednost (A3 u gornjem primjeru).
IFERROR vs IFNA
IFERROR tretira sve vrste pogrešaka, dok IFNA tretira samo grešku #N/A.
Prilikom rukovanja pogreškama uzrokovanim VLOOKUP -om morate se pobrinuti da koristite ispravnu formulu.
Koristite IFERROR kada želite liječiti sve vrste pogrešaka. Pogrešku sada mogu uzrokovati različiti čimbenici (poput pogrešne formule, pogrešno napisanog imenovanog raspona, ne pronalaženja vrijednosti pretraživanja i vraćanja vrijednosti pogreške iz tablice pretraživanja). IFERROR -u to ne bi bilo važno i zamijenilo bi sve ove pogreške navedenom vrijednošću.
Koristite IFNA kada želite liječiti samo #N/A pogrešaka, koje su vjerojatnije uzrokovane time što formula VLOOKUP ne može pronaći vrijednost pretraživanja.
Također bi vam mogli biti korisni sljedeći Excel vodiči:
- Kako VLOOKUP osjetiti velika i mala slova
- VLOOKUP vs. INDEKS/UTAKMICA - Rasprava završava ovdje!
- Upotrijebite VLookup za dobivanje posljednjeg broja na popisu u Excelu.
- Kako koristiti VLOOKUP s više kriterija
- Pogreška #NAME u Excelu - što je uzrokuje i kako je popraviti!
