Common VLOOKUP errors in MS EXCEL

In the above sample data we have some random data showing the quarterly sales of employees in one company. We have the data information  of an employee here  as employee number, employee name, location and their quarterly sales.

From the sample data if we have to find any particular data or to say we need to know the sales of any particular employee we can use the mighty vlookup function of MS Excel to the desired result.

For example to get the sales achieved by Kate, we can write our vlookup formula as

=VLOOKUP(“Kate”,C2:E12,3,0)

Here we have manually wrote the search field which is “kate” and we know that the data related to employee name and the sales are in range of C2 to E12 rows and columns that is what we chose our range of data to look for and the next attribute is 3 which is where the sales column we have we get to the number 3 by counting from employee name column as 1, location column as number 2 and sales as column 3. That’s why we said to our look to give the corresponding result from the 3rd column from the lookup value. Last attribute we chose as 0 as we want to have the true value or an exact match.

why vlookup is not working

Limitation: 

1. Lookup column must be on left most of the search area
2. Duplicate values in search column. 
3. Search area must be sorted if you want to use true
4. Unable to perform Multiple Criteria
5. Decimal point values must be less than 5 decimal places

Most Common vlookup errors

The most common error with vlookup in excel are 

1. #NA or vlookup na error
2. #Ref!
3. #Value!

Lets see the above limitations and errors result in more details. 

Section: #N/A error  or vlookup na error

The VLOOKUP function requires that the first argument be an exact match to the value you are searching for. In this case, your range is not in the correct order, which means that it does not contain all of the data required by VLOOKUP. For example, if you are looking for a value in cell B2, your range should include B2:B3.

Another example lets see the above table we get #NA error or vlookup na error,  as we were trying to get the employee code of a particular employee but in our table the result value is on the left rather than the lookup value thus the formula gives us an error value. This could be a limitation of the vlookup formula where it cannot look for the result which are on the left side of the data table.

One way to solve this is to keep the employee number on right of the employee name. but still it will be difficult to get the employee name searched based on employee number.  You can either modify the data based on search criteria or use an alternative rather more powerful formula xlookup().

Section: #REF! Error 

When you try to use the VLOOKUP function, Excel returns this error message: #REF! error. This means that there is no cell that contains the value you are looking for. One possible explanation is that the cell you are looking for may have been deleted. Another possibility is that there may be a problem with your formula. It’s also possible that there is an issue with the data in your spreadsheet. If you get this error message, try these steps: Make sure you are looking for a value in the correct column. Check your spelling and case of the word. The first thing to do is verify that you are looking for a value in the correct column. Sometimes people will inadvertently type something like “=VLOOKUP(A1,B2:C3)” instead of “=VLOOKUP(A1,B2:C3,4)”.

Section: #VALUE! Error 

This error is a general error which happens to tell that there is something wrong with the range and/or reference, or it could be a mistake in the formula. Sometimes it gets tricky to find and resolve this error. It Is most common to look into the range or any cell value containing hidden spaces, numbers typed or formatted as text, any kind of special character. The other general occurrence of a #VALUE! Error is when you perform a simple subtraction between two numbers.

In some cases – (minus) symbol which is used for the subtraction can be used to separate the list items so when you perform the subtraction with ‘-’ symbol it doesn’t take it as an operator. This can be easily fixed by going to Additional Settings in MS EXCEL and change the List Separator to something else most common is comma ‘,‘ and you can edit the subtraction formula and get the desired value instead of #VALUE! error.