In this tutorial, we will learn what the #N/A Error in Excel is and why and when we get it in our calculations.
Facing errors in Excel is the last thing we will want to see working with Excel. It’s quite frustrating when we see it appear everywhere, especially we don’t know where it comes from and what causes it.
However, fixing this error is overwhelming and determines our expertise in Excel.
Here’s how to get rid of any #REF! errors that come up in your worksheets and a few things you can do to prevent them.
What is #N/A Error In Excel?
The #N/A error is an error that tells data entered can’t be identified or found moreover it could be misspelled data in a formula. Eventually, this error commonly occurs in lookup functions.
Why N/A error Appear?
#N/A error appears for two possible reasons:
- The first reason is an inability to locate or find entered data.
- The second reason is misspelled information forcing Excel to look for incorrect information in worksheets.
#N/A errors occur most commonly, though not consistently, with lookup functions. If that’s true for your case, here’s what you can do about it:
Commonly, #N/A errors occur with lookup functions, though not consistently. If it is the case, the following procedure can do about it.
- Check the lookup value and lookup table for accuracy. Check if everything is spelled correctly and that there are no unnecessary blank characters.
- Examine the lookup table to see if it contains all of the values required by the lookup function.
- Check to see if the lookup range is complete.
- Verify if the lookup value data type and lookup table match. Both should be text strings, numeric values, or something similar.
Meanwhile, you can catch the #N/A error to quickly clean up your worksheet. Let’s go over some examples and see how it works on a spreadsheet.
How To Remove #N/A Error in Excel
This #N/A error can be fixed using a variety of methods, and we’ve provided a list of them below.
- The Data must be verified if it is complete.
- Confirm if has extra spacing.
- Inspect whether any data has been missed.
- Whether there is more information in the lookup table.
Errors can also be corrected using a formula like:
- IFERROR
- IFNA
Error fixed using IFERROR
The IFERROR function can be used to catch #N/A errors. In fact, the IFERROR function can catch any error and return a customized result.
In our example, you can see the N/A error appears in cell C2 because the lookup value does not exist in the lookup table in the range of A2:B:10.
=VLOOKUP(D5,A2:B10,1,0) "Branch D" is not found
To rectify this error, wrap the IFERROR function around the VLOOKUP formula as shown below:
=IFERROR(VLOOKUP(D5,A2:B10,1,0), "No Branch Found")
Hence, the VLOOKUP function returns an error the IFERROR function cathes it and return “No Branch Found”.
Error fixed Using IFNA
IFNA function only helps with #N/A errors; it does not help with other errors, such as the #N/A error in cell C2.
Using the IFNA formula, we can correct it and get a “Not Found” result in cell C2.
=IFNA(VLOOKUP(D5,A2:B10,2,0),"Not Found")
Final Thoughts
That’s all we need to know about #N/A error in Excel. So in this tutorial, we covered the possible reason along with possible ways to get rid of this error in our worksheet.
Now that you know what caused this error, you can easily troubleshoot your calculations when you encounter this type of error.
I hope this tutorial is useful in doing your task. Thank you for reading 🙂