Excel Yourself

Excel Yourself

Blackwood, Neale


Q: How do I get Excel to Ignore #N/A errors?

A: The #N/A error occurs when Excel can’t find something it is looking for, typically in a VLOOKUP or MATCH function with an exact search type selected. Usually you want to see the error because it could be an error that needs correction. Other times you want to ignore it. The following is a typical VLOOKUP formula.


Note the 0 before the closing bracket. This specifies an exact match is required and the #N/A error will be displayed if Excel can’t find an exact match for the contents of cell A1 in the first column of the range named WagesTable.

If the above formula returns the #N/A error and we want to ignore it and display 0 instead, we would use the following formula.

=IF(ISNA(VLOOKUP(A1,WagesTable,2,0)) ,0, VLOOKUP(A1,WagesTable,2,0))

ISNA will return True if there is an #N/A error and False if not. There are a number of other IS functions in Excel. Do a search in Help for IS Functions to see a listing.

Neale Blackwood CPA is a senior consultant with mallbarrow.com, which provides Excel consulting services. Email him at nblackwoodemailbarrow.com

For back issues go to www.cpaaustralia.com.au/links?excel

Copyright CPA Australia Feb 2007

Provided by ProQuest Information and Learning Company. All rights Reserved