Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Locate and Resolve the #N/A Error in Excel

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. 

Woman working on her laptop.

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. 

Vlookup example in excel.

When that request is changed to something valid, such as a “Football,” this error goes away, and a correct result is returned. 

Vlookup example.

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”)

If error vlookup example.

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. 

Fixing an error in excel example.

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

Convert text to column wizard.

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. 

Vlookup example.

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc