Fix Errors using VLOOKUP in Excel

Errors in VLOOKUP

How to fix errors using VLOOKUP? There are mainly four types of errors that can occur while performing a VLOOKUP in excel. The errors which can arise are:

1. #NA Error
2. #VALUE Error
3. #NAME Error
4. #REF Error

Before going on to evaluate what’s causing the above errors, here’s some points which everyone should follow to get error free results from VLOOKUP:

• The cell values should not have leading or trailing spaces.
• The cell values should be properly formatted.
• If an error occurs, try sorting the data present in the table.
• Make sure there are no blank cells in the lookup range.
• Make sure you are referring to the proper column number.
• Do not make silly mistakes like mistyping a LOOKUP value.
• Make sure numerical data is not present in textual format. If present, format the data appropriately.
• VLOOKUP is case sensitive, be aware of the case of the data set.

Make sure you follow the above best practices listed to avoid any type of error when using VLOOKUP. To fix errors using vlookup.

If you are facing any of the errors mentioned above. Let us see some examples to understand what’s going wrong and what can be done to fix errors using vlookup.

#NA Error

Suppose we have the following data:

Now we will perform a VLOOKUP with the cell C3 as the lookup value.

Notice here that we have got a #NA error. But the formula we have written is 100% correct.

What could go wrong?

On close inspection, it is found that the number which has been entered in the C3 cell is not formatted as a number but is in text format. From the alignment also you can determine that it is a text formatted cell.

To resolve this,

Click on the warning button and then click on Convert to Number.

Voila! We got our output.

This is only a single way through which you could get #NA error. 

One more example case where you can get this error is when the column_index_num is provided incorrectly and VLOOKUP is not able to find the lookup value in the given column.

#VALUE Error

This error can happen due to couple of reasons:

1. When Lookup Value exceeds 255 characters

Look whether due to a faulty key or your children’s mischief has led you to this error.

2. Path errors when taking values from another workbook.

If you are pulling data from another workbook, you have to include the full path to that file. You have to enclose the workbook’s name along with its extension in square brackets [], and then specify the sheet’s name followed by the exclamation mark. Also, you should have apostrophes around all this in case either a workbook or spreadsheet name contains spaces.

Here’s a correct Syntax which you can follow to avoid any mistakes:

=VLOOKUP(lookup_value, ‘[workbook name]sheet name’!table_array, col_index_num, FALSE)

An example of an implemented formula:

=VLOOKUP($A$2,'[New Workbook Class2.xls]Sheet1′!$B:$D,5,FALSE)

The above formula will search for the value of cell A2 in column B of Sheet1 in the “New Workbook Class2” workbook, and return a matching value from column D.

If any element of the path is missing, your VLOOKUP formula won’t work and return the #VALUE error (unless the lookup workbook is currently open).

#NAME Error

The Name error could occur due to typo errors you may have committed while giving function name or arguments.

In the below example:

It is clearly visible that the last argument has a typo error. It should be replaced with FALSE to solve this issue.

If this is not the case with you then check function name or arguments to find your error and correct them.

#REF Error

This is a very common error. It occurs when you give incorrect reference to a column or cell. It can also occur when you copy the formula to another cell and excel changes the formula based on relative positions.

In that case you could change the arguments to follow the Absolute Reference type.

An example #REF error:

Here in the above snapshot, it is clear that we are referencing the wrong column to extract the values from the VLOOKUP function. Changing the 3^rd argument of the function to 2 gives the correct solution.

Try for yourself to see both the error case as well as the resolved case.

Removing Error Messages of VLOOKUP using IFERROR function

We can use the IFERROR function to suppress the error messages produced while using VLOOKUP.

Syntax

=IFERROR(value, value_if_error)

Using the below formula on our previous example 

=IFERROR(VLOOKUP(C3,A1:B6, 2, FALSE),”Not Found”)

Now here the VLOOKUP works perfectly fine when the ID given is present in the table. In case of alien ID’s, it will print “NOT FOUND”, which is way more convenient than having an error message thrown in front of you.

Hope you found this article to be very informative.

You can now PRACTICE VLOOKUP online with Technoexcel for free without any additional installation.

Thanks for reading. Keep Learning! Peace.