How to Replace #N/A error in Excel Formula & Vlookup?
In many of the Excel formula, we get #N/A as return value. Espcially we get this quite often in Vlookup formula.
If we use this formula in a table or report, these error values fills up in many places & spoils the look & feel of the report.
To avoid this, you might replace all the #N/A error values with a zero or a blank.
Lets see how to do this with an example …
Assume you have a table with employee details & revenue like this:
Now, we apply a vlookup formula from another table to get revenue of each employee by using the name as in this image.
You can see that in the Vlookup we get #N/A error. And in the next column we replace it with a 0 using the IFERROR formula.
Formula explanation to Replace #NA in Excel
Here is little more explanation about this formula.
'// Replacing #N/A with 0 using Formula. ' =IFERROR (expression, value-if-error) =IFERROR(VLOOKUP(2,2,1,FALSE),"0") ' - Display value if no error ' - Display 0 if error '//Replace #N/A with blank =IFERROR(VLOOKUP(2,2,1,FALSE),"")
This IFERROR function also handles #VALUE, #NAME, #DIV/0 errors. Almost any error is handled by this function and you can replace the correspoding error with any value of your choice.
There is also another function with a slight variation is available. It is ISNA. This function cannot be used directly to replace a return value, but can be used to detect the error.
Meaning this function will return TRUE if the expression within it is an error value. Read this topic to know more about the #N/A & other error handling formula.
- Here is another topic that discusses the same in detail.