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.

**Table of Contents**Expand

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

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

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

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

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

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

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

#### 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 i**nto 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

- Why VLOOKUP Returns #N/A When Match Exists
- VLOOKUP Is Returning Just Formula Not Value in Excel
- Excel VLOOKUP Returning Column Header Instead of Value

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