a Sift Media publication
Over 23,000 pages of accounting passion and insight!   |   Sift Media logo
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

  • 2134 reads


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)

Posted by Anonymous on 05/15/2009 - 15:03
  • 1 read

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

Posted by rcvc1 on 05/08/2009 - 15:48
  • 3 reads
Welcome Visitor!
Sign up for the Weekly Insight newsletter to stay informed of future content in this category.
Email:
Already have an account? Sign in:
Forgotten your password?
Join us FB Connect with us LI Follow us
Voice of the Editor
Amidst a certain amount of controversy, the AICPA and the Chartered Institute of Management Accountants have launched a new designation for global management accountants, the CGMA (Chartered Global Management Accountant). The designation is available to members of both organizations.
Read more >>

Gail Perry, CPA
Editor-in-Chief, AccountingWEB
editor@accountingweb.com