Mashhura Jahan
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.
Download Practice Workbook
You can download the practice workbook from here.
Introduction to VLOOKUP Function
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.
9 Reasons with Solutions Why VLOOKUP Is Not Returning Correct Value in Excel
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. But 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.
Read More: VLOOKUP Not Picking up Table Array in Another Spreadsheet
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.
Read More: VLOOKUP Is Returning Just Formula Not Value in Excel
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 can be 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.
- 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 Function Not Calculating Automatically
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.
- 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.
Read More: Excel VLOOKUP Returning Column Header Instead of Value
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.
Read More: [Fixed!] Excel VLOOKUP Not Working Due to Format (2 Solutions)
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.
Read More: [Fixed] Excel VLOOKUP Returning 0 Instead of Expected Value
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.
Read More: [Solved]: Excel VLOOKUP Not Working with Numbers
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.
Read More: [Fixed!] Excel VLOOKUP Returning #N/A Error (6 Possible Solutions)
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 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.
Read More: [Fixed!]: VLOOKUP Function Is Returning Same Value in Excel
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. For more articles stay connected with ExcelDemy. If you have any questions then let me know in the comment section below.
