How to hide #N/A errors in Excel

What does the #N/A error code mean?

#N/A means “Not Available”. Excel formulas usually return this in situations where a requested value could not be found for some reason.

One of the most common causes of the #N/A error code is the VLOOKUP function. If a VLOOKUP function can’t find a matching value it will return #N/A.

If you’re unfamiliar with Excel formulas and functions you could benefit greatly from our completely free Basic Skills E-book.
All of Excel’s error codes are explained in depth in our Expert Skills Books and E-books.

Hiding #N/A codes

Here is an example:

Column C is using VLOOKUP to return prices from the table in columns F and G. In cell C6 the VLOOKUP function was unable to find a matching product so it returned a #N/A error.

In many cases this is what you would want to happen; if a price is missing it could be a genuine error that needs to be corrected. In some cases you might want to suppress the #N/A error. To do that, you’d use the IFERROR function.

IFERROR allows you to specify what should be returned if an error occurs. To replace any missing prices with zero, you could use this formula:

=IFERROR(VLOOKUP(A6,$F$2:$G$4,2,FALSE),0)

Surrounding the VLOOKUP formula with IFERROR lets you choose what will happen if an error is returned. In this case you’ve specified 0 when an error occurs, but you could make it display “Price Missing” with this formula:

=IFERROR(VLOOKUP(A6,$F$2:$G$4,2,FALSE),”Price Missing”)

IFERROR and VLOOKUP are explained in depth in our Expert Skills Books and E-books.

You can download an example workbook showing this formula in action.

Leave a Comment

Your email address will not be published.