Buzz
In previous articles, Mytour has guided you through ways to fix #NUM errors in Excel. In this article, Mytour will continue to guide you through some ways to fix the #N/A error in Excel.
1. #N/A Error in Excel
#N/A Error in Excel occurs when specific information or important data cannot be found or identified, such as misspelled employee names in a spreadsheet or non-existent color options. Other causes of the #N/A Error in Excel may include unnecessary whitespace characters, spelling mistakes, or incomplete lookup tables.
This error primarily appears in classic lookup functions in Excel, including VLOOKUP, HLOOKUP, LOOKUP, and MATCH functions.
The best way to prevent the #N/A Error is to ensure that lookup values and lookup tables are accurate and complete. To avoid the #N/A Error, check:
- The lookup value is spelled correctly and does not contain whitespace characters.
- The values in the lookup table are spelled correctly and do not contain whitespace.
- The lookup table contains all necessary values.
- Provide a lookup range for the function.
- The lookup value type matches the lookup table type (i.e., both must be text or both must be numbers, etc.).
Note: If the #N/A error occurs, the result you receive will not be accurate. Ensure you have accurately matched the configurations. Approximate match mode will return all inaccurate results.
2. Fixing #N/A Error in Excel
Method 1: Using IFERROR function to trap #N/A error
To fix the #N/A error, the first solution is to use the IFERROR function to trap the error. The IFERROR function can catch any error and return a substitute result.
In the example below, the #N/A error appears in cell F5 because 'ice cream' does not exist in the lookup table.
=VLOOKUP(E5,data,2,0) // 'ice cream' is not found
To fix this error, use the IFERROR function to wrap the VLOOKUP formula as follows:
=IFERROR(VLOOKUP(E7,data,2,0),'Not found')
If the VLOOKUP function returns an error, the IFERROR function will catch it and return the result 'Not found'.
Method 2: Trap #N/A error using IFNA function
The IFNA function can also trap and handle specific #N/A errors. The syntax for using the IFNA function is similar to the IFERROR function:
=IFERROR(VLOOKUP(A1,table,column,0),'Not found')
=IFNA(VLOOKUP(A1,table,column,0),'Not found')
The advantage of using the IFNA function is that it specifically targets the #N/A error. In contrast, the IFERROR function catches any error detected by the function. For example, even if you enter the VLOOKUP function name in lowercase, the IFERROR function will still return the 'Not found' error.
No Message Displayed
If you don't want to display any message when trapping the #N/A error (i.e., display an empty cell), you can use an empty string ('') as follows:
=IFERROR(VLOOKUP(E7,data,2,0),'')
Method 3: Using INDEX and MATCH functions to fix #N/A error
The MATCH function also returns the #N/A error if the function cannot find a value. If you are using a combination of INDEX and MATCH functions, you can trap the #N/A error in the same way. In the example above, the formula in cell F5 will be:
=IFERROR(INDEX(C5:C9,MATCH(E5,B5:B9,0)),'Not found')
Method 4: Forcing #N/A error
If you want to force the #N/A error on a spreadsheet, you can use the NA function. For example, to display the #N/A error in a cell when the value in cell A1 equals 0, you can use the following formula:
=IF(A1=0, NA())
This article has just guided you through some ways to fix #N/A errors in Excel. Additionally, readers can refer to more extremely useful Excel tips and tricks articles on Mytour to learn some effective Excel usage tips.
