HOW TO REMOVE VLOOKUP #N/A error in Excel
VLOOKUP without #N/A error
Want to learn how to remove the #N/A error when VLOOKUP gives you on excel? This post will give you an overview of how to do so using the IFERROR function in excel.
VLOOKUP is one of the most useful Excel functions. It searches for the value you specify and gives back a matching value from another column. In order to conceal the #N/A error that VLOOKUP pops up when it fails to find the value, you can use the IFERROR function to the error #N/A error and change to any value of your choice.
Before you start the process of using the IFERROR function to remove the #N/A error given by VLOOKUP, you must have a table ready from which you want to change the way the error message displays. See an example in Figure 1 below.
Figure 1: A table prepared ahead of using the IFERROR function
Next, try to look for a certain student’s score using the VLOOKUP function. To do this, draw a small table with provision for the student’s name and the score. In the example below, the student whose score we are looking for is Jane and her score is 75. We want to find out whether she is on the list.
- Go to Formulas on the menu bar and click on Lookup & Reference.
- Select VLOOKUP on the menu that drops down.
- A dialogue box shown below will appear. Fill in the required fields.
Figure 2: Accessing the VLOOKUP function
- The Lookup value is the score we want to look for in the table on the left. It is 75 in our example. Type 75 into the field.
- Table array refers to the entire range of the table on the left. It extends from A5 to B11. Type A5:B11 in the field.
- Column index number refers to the numerical position of the column containing the scores among which 75 is being looked up for. Since scores are in the second column in our example, the value is 2.
- Type 0 in the Range lookup to ensure that VLOOKUP insists on finding the exact figure being searched and not any other no matter how close it is to the one being searched.
- Thereafter, click on OK.
Since 75 is not among the scores in the table on the left, VLOOKUP will return an #N/A error as shown below.
Figure 3: #N/A error shown by VLOOKUP
Another means of getting the result displayed in the figure above is by simply typing the formula =VLOOKUP(75,A5:B11,2,0) into the cell and pressing the enter key.
Now, if you wish to change the #N/A error to “NOT FOUND”, do the following:
- Enter this formula:
- After typing the formula, hit Enter on the keyboard.
The result will display as shown below:
Figure 4: Replacing #N/A error with NOT FOUND
However, if you wish to make the cell blank without sowing the #N/A error, do the following;
- Enter this formula: =IFERROR(VLOOKUP(75,A5:B11,2,0),“ ”)
- Press Enter.
Notice that by simply varying this formula, you can customize the error message to display anything of your choice.