There are two situations when you are dealing with #N/A:
- You don’t want to have the #N/A error.
- You don’t want to show it to the user.
This tutorial will help you to deal with these situations.
#N/A Error – What Does it Mean?
As its name says, “Not Available”. In Excel, when you are looking for a value by using lookup functions and that value is not in the list or the range, your formula will likely return the result as an #N/A error.
When You Get the #N/A?
In the above example, we have used the VLOOKUP to look up the value 7 in column A. But as you can see, “7” is not in the range, and the formula returns #N/A in the result.
In the same way, when you use the MATCH function and try to get a match for “7”, the formula returns #N/A in the result.
Apart from this, if you use another of the LOOKUP formula, like, HLOOKUP, LOOKUP, and INDEX + MATCH, you will also get the same error.
Note: With the XLOOKUP, you have an inbuild argument that allows you to define the values to get if the value you are looking for is not in the lookup range.
How to Deal with #N/A?
There are two specific functions that you can use.
IFNA: Only for #N/A Error
In the below example, we have used the MATCH by wrapping it with the IFNA. In the IFNA, the first argument used the MATCH function and a value to return if MATCH returns the #N/A.
IFERROR: For all Errors
In the below example also, we have used the IFERROR with VLOOKUP. We have wrapped VLOOKUP with IFERROR and specified the value to return if there’s an error in the result.
Other Points to Remember
- It’s better to verify the value you are looking for with any lookup function. If you have misspelled value, but the correct value is there, it will be hard for you to know this.
- Sometimes, you can get the #N/A error when the values you are looking for are in a different format than the same ones you have in the lookup range. For example, if you are looking for a number but that number is saved as a text in the lookup range, you will still get the #N/A error.
- Having extra spaces in the lookup value or the values in the lookup table can also result in a #N/A error.