[Fixed!] Excel VLOOKUP Not Returning Correct Value

Get FREE Advanced Excel Exercises with Solutions!

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.

VLOOKUP Not Returning Correct Value


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.

Excel Vlookup Not Returning Correct Value because of Not Defining Match Type


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)

Define Preferred Match Type Correctly when VLOOKUP is Not Returning Correct Value in Excel

  • 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.

Using Wrong Column Index Number and VLOOKUP Not Returning Correct Value


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)

Use Column Index Number Correctly When Vlookup Not Returning Correct Value

  • 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.

Vlookup Not Returning Correct Value because of Not Using 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)

Employ Absolute Cell Reference When Vlookup is Not Returning Correct Value

  • Thirdly, press Enter to get the result.

Dragging Fill Handle to Copy The Formula When Vlookup is not Returning Correct Value

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.

Read More: [Fixed] Excel VLOOKUP Returning 0 Instead of Expected Value


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.

Vlookup is not Returning Correct Value If New Rows Are Added to Range


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.

Use Table Instead of Range When Vlookup is Not Returning Correct Value

  • 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.

Vloookup is Not Returning Correct Value because of 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.

Change Cell Format & Use Find and Replace Feature

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

Change Cell Format to General When Vlookup is Not Returning Correct Value

  • 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.

Using Find and Replace Feature When Vlookup is Not Returning Correct Value

  • 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.

Having Extra Space in Lookup Value and That's Why Vlookup is Not Returning Correct Value

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)

Use Excel TRIM Function When Vlookup is Not Returning Correct Value

  • 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.

Vlookup Not Returning Correct Value because of Storing Numbers as Text

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.

Employ Paste Special Option When Vlookup is Not Returning Correct Value

  • 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.

Vlookup Not Returning Correct Value Because of Searching From Left Side of Lookup 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))

Apply INDEX and MATCH Functions When Vlookup is not Returning Correct Value

  • 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.

Read More: [Fixed!] Excel VLOOKUP Returning #N/A Error


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.

Vlookup Not Returning Correct Value because Table Containing Same Lookup 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.

Use Pivot Table Instead of VLOOKUP When it is Not Returning Correct Value

  • 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.

Read More: [Fixed!]: VLOOKUP Function Is Returning Same Value in Excel


Download Practice Workbook

You can download the practice workbook from here.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo