[Fixed!] Excel VLOOKUP Not Returning Correct Value

Excel VLOOKUP function is a very useful function. You can find out your needed data from a big range of data easily by using the VLOOKUP function. But sometimes the VLOOKUP function does not return the correct result. The focus of this article is to explain why Excel VLOOKUP is not returning the correct value with suitable solutions.

Introduction to VLOOKUP Function

The VLOOKUP function generally looks for a given value in a data range and then returns the exact match or approximate match of that value from another column.

Syntax:

The syntax of the VLOOKUP function is:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Arguments:

Here, the lookup_value is the given value, table_array is the range where you want to look for a match, col_index_num is the column from where you want to return the result, and range_lookup is the match type you want. The range_lookup is an optional argument here. And, the rest of the arguments are required.

Why VLOOKUP Is Not Returning Correct Value in Excel: 9 Reasons with Solutions

Here, I have taken the following dataset to explain this article. I used the VLOOKUP function to find the marks in Physics for a student named Natalie. But, you can see that the VLOOKUP function is not returning the correct result. Now, I will explain why VLOOKUP is not returning the correct value with solutions in Excel.

Reason-01: Not Defining Match Type

In the following image, you can see that I have applied the VLOOKUP function to find a match. However, the VLOOKUP function is not returning the correct value in Excel. The reason behind it is not defining the match type. You can see that I skipped the range_lookup argument here. Excel takes it as TRUE by default. Which means approximate match. That is why the function is not returning the value I want.

Solution: Define Preferred Match Type Correctly

You can solve this problem easily by defining the preferred match type correctly. You will have to select FALSE when you want an exact match and TRUE when you want an approximate match.

Let’s see the steps.

Steps:

Firstly, select the cell where you want the result. Here, I selected cell G5.
Secondly, in cell G5 write the following formula.

=VLOOKUP(F5,B5:D11,2,FALSE)

Thirdly, press Enter to get the result.

Here, in the VLOOKUP function, I selected cell F5 as lookup_value, B5:D11 as table_array, 2 as col_index_num, and FALSE as range_lookup. The function will look for an exact match for the value in F5 from column 2 of cell range B5:D11. And, return it as a result.

Reason-02: Using Wrong Column Index Number

Here, you can see that the VLOOKUP function is not returning the correct value. The reason behind this is using the wrong column index number. In the VLOOKUP function, I selected 2 as col_index_num but the marks for math are in column 3.

Solution: Use Column Index Number Correctly

This problem can be solved easily by selecting the column index number correctly. Let me show you how you can do it.

Steps:

In the beginning, select the cell where you want to find the match. Here, I selected cell G5.
Then, in cell G5 write the following formula.

=VLOOKUP(F5,B5:D11,3,FALSE)

Next, press Enter and you will get your desired result.

Now, in the VLOOKUP function, I selected cell F5 as lookup_value, B5:D11 as table_array, 3 as col_index_num, and FALSE as range_lookup. The function will look for an exact match for the value in F5 from column 3 of cell range B5:D11. And, return it as a result.

Reason-03: Not Using Absolute Cell Reference

In the following image, you can see that the VLOOKUP function is returning the correct value in 2 cases, and in one case it is returning an error. The possible reason why the VLOOKUP function is not returning the correct value is not using the Absolute Cell Reference.

Here, you can see that I used relative cell reference in the VLOOKUP function. It works for the first value but if you drag the Fill Handle to copy the formula then the table_array changes and the function returns the wrong value or error.

Solution: Employ Absolute Cell Reference

Absolute Cell Reference fixes a range or a cell. By using it you can make the table_array fixed. So that the formula does not change while you drag the Fill Handle to copy the formula. Let’s see the steps.

Steps:

Firstly, select the cell where you want the result.
Secondly, write the following formula in that selected cell.

=VLOOKUP(F5,$B$5:$D$11,2,FALSE)

Thirdly, press Enter to get the result.

Here, in the VLOOKUP function, I selected cell F5 as lookup_value, B5:D11 as table_array, 2 as col_index_num, and FALSE as range_lookup. The function will look for an exact match for the value in F5 from column 2 of cell range B5:D11. And, return it as a result. I used Absolute Cell Reference so that the formula does not change while using Autofill.

After that, drag the Fill Handle down to copy the formula.

Finally, you can see that I have copied the formula to the other cells and got my desired output.

Reason-04: If New Rows Are Added to Range

Here, you can see that the VLOOKUP function is returning the correct value for some cases and the wrong values for some cases. The possible reason behind this can be adding new rows to the range after writing the formula.

Solution: Use Table Instead of Range

You can solve this problem easily by using a table instead of a range. Let’s see how you can do that.

Steps:

In the beginning, select the range.
Next, go to the Insert tab.
Then, select Table.

After that, the Create Table dialog box will appear.
Check the My table has headers option.
Then, select OK.

After that, a table will be inserted.

Then, select the cell where you want to find the match. Here, I selected cell G5.
Afterward, in cell G5 write the following formula.

=VLOOKUP(F5,Table2[#All],3,FALSE)

Further, press Enter to get the result.

Here, in the VLOOKUP function, I selected cell F5 as lookup_value, Table2[#All] as table_array, 3 as col_index_num, and FALSE as range_lookup. The function will look for an exact match for the value in F5 from column 3 of Table2[#All].

After that, drag the Fill Handle down to copy the formula.

Here, you can see that I have copied the formula to the other cells.

Then, I added two more rows to the table.

Now, you can get the correct results for the new values by simply dragging the Fill Handle.

In the end, you can see that I have copied the formula to the other cells and got my desired results.

Reason-05: Selecting Cell Format as Text

In the following image, you can see that the Excel VLOOKUP is not returning the correct value. It returns the formula as it is. The reason behind this is selecting cell format as Text.

Solution: Change Cell Format & Use Find and Replace Feature

This problem can be solved easily by selecting the right cell format and then using the Find and Replace feature. Let’s see the steps.

Steps:

Firstly, select the cell where the VLOOKUP is not returning the correct value.
Secondly, go to the Home tab.
Thirdly, select the drop-down option for selecting cell format.

After that, a drop-down option will appear.
Select General.

Now, the cell format is changed to General but the VLOOKUP is still returning the same result.

To fix that, press Ctrl + H from your keyboard.
After that, the Find and Replace dialog box will appear.
Next, write “=” in the Find what section.
Then, write “=” in the Replace with section.
Finally, select Replace.

Now, you will see that the VLOOKUP is returning the correct results.

Reason-06: Having Extra Space in Lookup Value

Here, you can see that I have used the VLOOKUP function correctly and written the formula properly. But, the VLOOKUP is not returning the correct value in Excel.

In the following image, you can see that the lookup_value contains an extra space after it and this is causing the problem.

Solution: Use Excel TRIM Function

You can simply delete the extra space to solve the problem. But for long data, this can be tiring and time-consuming. So, using the TRIM function will make your task a lot easier. Let me show you how you can do it.

Steps:

First, select the cell where the VLOOKUP is returning an error. Here, I selected cell G5.
Then, in cell G5 write the following formula.

=VLOOKUP(TRIM(F5),$B$5:$D$11,3,FALSE)

Next, press Enter to get the correct result.

🔎 How Does the Formula Work?

TRIM(F5): Here, the TRIM function removes the extra spaces from the lookup_value.
VLOOKUP(TRIM(F5),$B$5:$D$11,3,FALSE): Now, in the VLOOKUP function, I selected TRIM(F5) as lookup_value, B5:D11 as table_array, 3 as col_index_num, and FALSE as range_lookup. The function will look for an exact match for the lookup_value from column 3 of cell range B5:D11. And, return it as a result.

After that, drag the Fill Handle down to copy the formula to the other cells.

Finally, you can see that I have copied the formula to the other cells and got the desired results.

Reason-07: Storing Numbers as Text

Another reason why Excel VLOOKUP is not returning the correct value can be storing numbers as text. Here, you can see the formula is correct but it is returning an error because of this reason.

In the following image, you can see that there is an apostrophe before the number as a result the numbers are stored as text.

Solution: Employ Paste Special Option

There are many ways of converting numbers that are stored as text. But, I will use the Paste Special option here. Let’s see the steps.

Steps:

Firstly, select a blank cell outside your dataset.
Secondly, press Ctrl + C to copy the cell.

Thirdly, select the range where the numbers are stored as Text.

Then, Right-click on the selected cells.
After that, select Paste Special.

Next, the Paste Special dialog box will appear.
Select Values from Paste.
Then, select Add from Operation.
After that, select OK.

Finally, you will see that you have converted the numbers and the VLOOKUP is also returning the correct result.

Reason-08: Searching from Left Side of Lookup Value

In the following image, you can see that in the VLOOKUP function the lookup_value is in column 3 and the col_index_num is 1. VLOOKUP can not do this kind of operation. As a result, the VLOOKUP function is not returning the correct value.

Solution: Apply INDEX and MATCH Functions

To find this type of match you can use the INDEX function and the MATCH function. Let me show you the steps.

Steps:

In the beginning, select the cell where you want to find the match.
Then, write the following formula in that selected cell.

=INDEX(B5:B11,MATCH(F5,D5:D11,0))

Next, press Enter to get the result.

🔎 How Does the Formula Work?

MATCH(F5,D5:D11,0): Here, the MATCH function finds out the exact match of the lookup_value from the lookup_array and returns the relative position of it in the array.
INDEX(B5:B11,MATCH(F5,D5:D11,0)): Now, the INDEX function returns the value from within the cell range B5:B11.

After that, drag the Fill Handle down to copy the formula to the other cells.

In the end, you can see that I have copied the formula to the other cells and got my desired result.

Reason-09: Table Containing Same Lookup Value

In this section, I will explain a different kind of situation. Here, you can see that the table contains the same lookup value multiple times. But, when you use the VLOOKUP function to find the match it only returns the first value.

Solution: Use Pivot Table Instead of VLOOKUP

To find a match in these situations, it is better to use the Pivot Table instead of the VLOOKUP function. Let’s see how you can do that.

Steps:

Firstly, select the cell range where you have your data.
Secondly, go to the Insert tab.
Thirdly, select PivotTable.

After that, the PivotTable from table or range dialog box will appear.
Select Existing Worksheet.
Then, select the location where you want the PivotTable.
Next, select OK.

Here, the PivotTable Fields Task Pane will appear on the right side of the screen.
After that, select and drag the fields where you want them. Here, I selected and dragged the State and Department into the Rows area. And, Sales into the Values area.

Now, you will see that you have inserted the Pivot Table.

Now, to filter Florida, Click on the Filter button.
Then, Check Florida.
After that, select OK.

Finally, you can see that I have got my desired results.

Download Practice Workbook

You can download the practice workbook from here.

VLOOKUP Is Not Returning Correct Result.xlsx

Conclusion

So, you have reached the end of my article. Here, I explained 9 reasons why the Excel VLOOKUP is not returning correct value with solutions. I hope this article was helpful to you. If you have any questions, let me know in the comment section below.

<< Go Back to Issues with VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

[Fixed!] Excel VLOOKUP Not Returning Correct Value

Introduction to VLOOKUP Function

Why VLOOKUP Is Not Returning Correct Value in Excel: 9 Reasons with Solutions

Reason-01: Not Defining Match Type

Solution: Define Preferred Match Type Correctly

Reason-02: Using Wrong Column Index Number

Solution: Use Column Index Number Correctly

Reason-03: Not Using Absolute Cell Reference

Solution: Employ Absolute Cell Reference

Reason-04: If New Rows Are Added to Range

Solution: Use Table Instead of Range

Reason-05: Selecting Cell Format as Text

Solution: Change Cell Format & Use Find and Replace Feature

Reason-06: Having Extra Space in Lookup Value

Solution: Use Excel TRIM Function

Reason-07: Storing Numbers as Text

Solution: Employ Paste Special Option

Reason-08: Searching from Left Side of Lookup Value

Solution: Apply INDEX and MATCH Functions

Reason-09: Table Containing Same Lookup Value

Solution: Use Pivot Table Instead of VLOOKUP

Conclusion

Related Articles

Leave a reply Cancel reply