When using the VLOOKUP formula in Excel, sometimes you may end up with the #N/A error. This happens when your formula cannot find the lookup value. In this tutorial, I will show you different ways to hide this #N/A error and get rid of this problem. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Generic Formula:
- Use the below formula based on IFERROR and VLOOKUP function to show something meaningful in place of the #N/A error.
=IFERROR(VLOOKUP(A1,table,column,FALSE),”Message”)
Syntax Explanations:
- IFERROR – This function will help to return a custom result when a formula generates an error and a standard result when no error is detected. Read more on the IFERROR Function.
- VLOOKUP – In Excel, the VLOOKUP function helps to lookup data in a range or table by row.
- FALSE – This function helps to return the logical value FALSE. Read more on the FALSE Function.
- Table – It represents the input ranges from your data.
- Column – It specifies the column number.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
Practical Example:
Let’s see the below example to get rid of the #N/A error or any other errors while using the VLOOKUP function.
Example 1:
- First, we will give the input values in Column B and Column C.
- Here, the normal VLOOKUP function creates a #N/A error.
- So, apply the above give formula to the formula bar section or any other cells where you want to show the result.
- Press the “Enter” key.
- Finally, it will return the result in the selected cell.
Example 2:
You can also use the IFNA function instead of the IFERROR function to solve this problem. Just use the below formula.
=IFNA(VLOOKUP(A1,table,column,FALSE),”Message”)
- IFNA Function – It will return a custom result when a formula generates the #N/A error, and a standard result will be displayed when no error is detected.
Apply the above-given formula to the formula bar section it will display the result as per the below image.
Bottom-Line:
From this tutorial, we have described the simple and useful formulas to hide the #N/A error in Excel with VLOOKUP along with IFERROR and IFNA functions. If you have any doubts, feel free to share them with us. We will assist you. Click here to know more about *Geek Excel **and Excel Formulas *!! **
Top comments (0)