When Excel doesn’t understand what you are asking it to do, it is probably going to return an error message. Depending on the type of error, your message may vary.
#N/A is a common error in Excel, and it often occurs when using the VLOOKUP function, but not always. Here are some of the most common causes of the #N/A error and how you can correct them.
If you’d prefer to get one-on-one assistance or more immediate help, live chat is available from Excelchat.
Learn how to find and troubleshoot the #N/A error in Excel.
Common Cause of the #N/A Error in Excel
Excel generally returns the #N/A error in response to some type of lookup function to tell you that item you are searching for isn’t identified or found. These functions include VLOOKUP, LOOKUP, HLOOKUP, and MATCH.
A simple error in the formula such as an extra space, a name or product misspelling, or an option that doesn’t exist can result in this error. For example, the VLOOKUP function asks for the price of a “Volleyball” from the list, but this isn’t one of the options in the “items” data range, so Excel returns a #N/A error.
When that request is changed to something valid, such as a “Football,” this error goes away, and a correct result is returned.
If you are using a lookup function and find that you have an unexpected error, some of the things you can quickly check include:
- The lookup value is spelled correctly and doesn’t include extra spaces
- Values in your lookup table are spelled correctly and don’t contain extra spaces
- Your lookup table contains the required data
- You named and reference your lookup range correctly
- Matching type (exact vs. approximate) is correct
In the example provided above, anything that isn’t a match in the VLOOKUP formula is going to return an #N/A error. In other words, if any of these are listed – footballs, Frisbee, or baseball – these will each get an error for different reasons.
Return an Alternative Result Instead of #N/A
Having an #N/A error on your worksheet doesn’t tell you much, other than you’ve missed something with a formula. You might want Excel to return a result that is more meaningful, and there are several ways to accomplish this.
You can use the IFERROR function to tell Excel to do something else if it finds an error in your lookup. In the prior example, “Volleyball” isn’t found and returns an #N/A error. If you rewrite the formula as follows, Excel will tell you that the result was “Not found” instead of returning an error:
=IFERROR(VLOOKUP(E6,items,2,0),”Not found”)
The IFERROR function will catch any type of error, but if you just want to target the #N/A error and return an alternative result, you can use the IFNA function. The syntax is the same for the formula:
=IFNA(VLOOKUP(E6,items,2,0),”Not found”)
If you would prefer that Excel returns a blank cell instead of a message when a result isn’t found or there is an error, you can change the instructions to an empty string as follows:
=IFERROR(VLOOKUP(E6,items,2,0),””)
Numbers Formatted as Text
Another common cause of the #N/A error in Excel is when you have numbers that are formatted as text, either in your lookup or main table. This could occur when you import data from an external source or if you’ve placed an apostrophe before a number to designated a leading zero.
Depending on your operation, this can return an #N/A error in Excel. you can tell if your numbers are formatted as text because they will have a green square in the top left corner, be left-justified, or both.
There are several ways to fix having numbers as text in Excel. The first is to highlight your numbers and right-click. Choose Format Cells and then adjust your formatting appropriately. You can also highlight your cells and go to the Data tab. Choose Text to Columns, and click Finish.
Extra Spaces in Your Data Table
Another cause of the #N/A error is extra spaces in your data table. This can be difficult to detect because you can’t see these with the human eye. If your dataset is small, you can just go through the items and see if you have some extra spaces. If they’re there, delete them.
One of the best ways to get rid of this error with a large dataset is to add the TRIM function to your lookup argument. This will tell Excel to ignore any extra spaces so that you won’t get errors.
Other Ways to Find and Fix the #N/A Error
You can use Excel’s Find & Select function to locate your errors in Excel. Just go to Find and type in #N/A or N/A in the Find What space. Then, hit either Find All or Find Next. You can use this to work through your errors and correct them.
When you have an error occur in Excel, you’ll get a small yellow icon to the left of the cell containing the error message. If you hover over this icon, there will be a drop-down box with some options to help you work through the error. Some choices are to review your formula, change it, and ignore the error.
Were you able to find and resolve your #N/A errors in Excel with this guide? Whether you’d rather skip the guide completely or still have some issues to troubleshoot, Excelchat can help. Our friendly and qualified Excel experts are standing by to walk you through anything related to Excel.
Just open a live chat window to get started 24/7. Your first session is always free.
Leave a Comment