Q&A (back to index)
Working around #N/A
Posted by lsd_7878 on 05/06/2009 - 22:54
An #N/A (missing data) populates a vlookup answer cell if the information is not found in the lookup table for an exact match (and I don't want to use a fuzzy TRUE match). This error message (the #N/A)prevents that cell from being useable (columns won't add and I haven't figured out how to eliminate it with IF statements buried in the vlookup statement either). Any suggestions how to work around this dreaded missing data issue?
Thanks!
Linda
Linda Decker
- 1592 reads
- login or register to post comments
- Add to a social bookmarking site
- Send to a friend
IFERROR
Another method available in Excel 07 is the IFERROR formula. The IFERROR has only 2 arguments. The 1st argument is "value". This is where you would place the VLOOKUP. The second argument is "value if error" where you would place a blank or a zero.
=IFERROR(VLOOKUP(value,array,column index,logical),0)
Use ISNA to trap errors
Excel uses the ISNA function to trap #NA errors. Combine this with a conditional statement to solve your problem.
Example: =IF(ISNA(Vlookup(a,b:c,FALSE)),0,VLOOKUP(a,b:c,FALSE))
Replace the zero with any other value you choose or make it a word or term by enclosing it on quotes.
Bob VC, CPCU, CLU, Florida