## The VLOOKUP Function

**The VLOOKUP function** looks for a given value in a data range and returns the exact match or an approximate match of that value.

**Syntax:**

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

**Arguments:**

The** lookup_value** is the given value, **table_array** is the range in which you to look for a match,** col_index_num** is the column from which the result is returned, and **range_lookup** is the match type. The **range_lookup** is an optional argument here.

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

In the following dataset, the **VLOOKUP** function was used to find the marks in **Physics** of **Natalie**.

It is not returning the correct value.

### Reason 1 – Not Defining the Match Type

The **range_lookup** argument was skipped. Excel takes it as **TRUE** by default, which means an approximate match.

#### Solution: Define Preferred Match Type Correctly

**Steps:**

- Select the cell in which you want the result. Here,
**G5**. - In
**G5**enter the following formula.

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

- Press
**Enter**to see the result.

**F5**is the

**lookup_value**,

**B5:D11**is

**table_array**,

**2**is the

**col_index_num**, and

**FALSE**is the

**range_lookup**. The function will look for an

**exact match**for the value of

**F5**in column

**2**in

**B5:D11**.

### Reason 2 – Using the Wrong Column Index Number

The** VLOOKUP **function is using the **wrong column index number**. In the **VLOOKUP** function, **2** was selected as **col_index_num** but the marks for math are in **column 3**.

#### Solution: Use the Column Index Number Correctly

**Steps:**

- Select the cell in which you want the result. Here,
**G5**. - In
**G5**enter the following formula.

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

- Press
**Enter**to see the result.

**F5**is the

**lookup_value**,

**B5:D11**is the

**table_array**,

**3**is the

**col_index_num**, and

**FALSE**is the

**range_lookup**. The function will look for an exact match for the value of

**F5**in

**column 3**in

**B5:D11**.

### Reason 3 – Not Using an Absolute Cell Reference

The **VLOOKUP** function is returning the** correct value** in **2** **cases**, and in **one case** it is returning an **error**. **Absolute Cell Reference **wasn’t used.

A relative cell reference was used in the **VLOOKUP** function. It works for the first value but if you drag the **Fill Handle** to copy the formula, the **table_array** changes and the function returns a wrong value or an** error**.

#### Solution: Apply an Absolute Cell Reference

**Steps:**

- Select the cell in which you want the result.
- Enter the following formula in that cell.

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

- Press
**Enter**to see the result.

**F5**is the

**lookup_value**,

**B5:D11**is the

**table_array**,

**2**is the

**col_index_num**, and

**FALSE**is the

**range_lookup**. The function will look for an exact match for the value of

**F5**in column

**2**in

**B5:D11**.

**Absolute Cell Reference**was used.

- Drag the
**Fill Handle**down to copy the formula.

This is the output.

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

### Reason 4 – New Rows Are Added to the Range

The **VLOOKUP** function is returning wrong values. **New rows** were added to the range after entering the formula.

#### Solution: Use a Table Instead of a Range

**Steps:**

- Select the
**range**. - Go to the
**Insert**tab. - Select
**Table**.

- In the
**Create Table**dialog box, check**My table has headers**. - Click
**OK**.

A table will be inserted.

- Select the cell in which you want to find the match. Here,
**G5**. - In
**G5,**enter the following formula.

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

- Press
**Enter**to see the result.

**F5**is the

**lookup_value**,

**Table2[#All]**is the

**table_array**,

**3**is the

**col_index_num**, and

**FALSE**is the

**range_lookup**. The function will look for an exact match for the value of

**F5**in column

**3**of the

**Table2[#All]**.

- Drag the
**Fill Handle**down to copy the formula.

This is the output.

Two rows were added to the table.

- See the correct results for the new values by dragging the
**Fill****Handle**.

This is the output.

### Reason 5 – Selecting Cell Format as Text

The **VLOOKUP** is not returning the correct value. It returns the formula as it is. Cell format is selected as **Text**.

#### Solution: Change Cell Format & Use the Find and Replace Feature

**Steps:**

- Select the cell in which the
**VLOOKUP**is not returning the correct value. - Go to the
**Home**tab. - Select the drop-down option to select cell format.

- Select
**General**.

- The cell format is changed to
**General**but the**VLOOKUP**is still returning a wrong result.

- Press
**Ctrl + H**. - In the
**Find and Replace**dialog box, enter**“=”**in**Find what**. - Enter
**“=”**in**Replace with**. - Click
**Replace**.

This is the output.

### Reason 6 – Having an Extra Space in the Lookup Value

The **VLOOKUP** formula is correct, but the correct value is not returned.

The **lookup_value** contains an **extra space**.

#### Solution: Use the Excel TRIM Function

**Steps:**

- Select the cell in which the
**VLOOKUP**is returning an error. Here,**G5**. - In
**G5**, use the following formula.

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

- Press
**Enter**to see the correct result.

**Formula Breakdown**

**TRIM(F5):**removes the extra spaces from the**lookup_value**.**VLOOKUP(TRIM(F5),$B$5:$D$11,3,FALSE):****TRIM(F5)**is the**lookup_value**,**B5:D11**is the**table_array**,**3**is the**col_index_num**, and**FALSE**is the**range_lookup**. The function will look for an exact match for the**lookup_value**of column**3**in**B5:D11**.

- Drag the
**Fill Handle**down to copy the formula to the other cells.

This is the output.

### Reason 7 – Storing Numbers as Text

The formula is correct but it is returning an error because numbers are stored as text.

There is an **apostrophe** before the number.

#### Solution: Using the Paste Special Option

**Steps:**

- Select a blank cell outside your dataset.
- Press
**Ctrl + C**to copy the cell.

- Select the range in which numbers are stored as
**Text**.

**Right-click**the selected cells.- Select
**Paste Special**.

- In the
**Paste Special**dialog box, select**Values**in**Paste**. - Choose
**Add**in**Operation**. - Click
**OK**.

It is returning the correct result.

### Reason 8 – Searching from the Left Side of the Lookup Value

In the following image the **lookup_value** is in column **3** and the** col_index_num** is **1**. **VLOOKUP** can not return the correct value.

#### Solution: Apply the INDEX and the MATCH Functions

**Steps:**

- Select the cell in which you want to find the match.
- Enter the following formula in the selected cell.

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

- Press
**Enter**to see the result.

**Formula Breakdown**

**MATCH(F5,D5:D11,0):**finds the exact match of the**lookup_value**from the**lookup_array**and returns its**relative position**in the array.**INDEX(B5:B11,MATCH(F5,D5:D11,0)):**returns the value in**B5:B11**.

- Drag the
**Fill Handle**down to copy the formula to the other cells.

- This is the output.

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

### Reason 9 – Table Containing the Same Lookup Value

The table contains the same lookup value multiple times. The **VLOOKUP** function returns the first value only.

#### Solution: Use a Pivot Table Instead of VLOOKUP

**Steps:**

- Select the data range.
- Go to the
**Insert**tab. - Select
**PivotTable**.

- In the
**PivotTable from table or range**dialog box, select**Existing Worksheet**. - Choose a location for the
**PivotTable**. - Click
**OK**.

- In the
**PivotTable Fields Task Pane**, select and drag the fields. Here, State and**Department**into**Rows**. And,**Sales**into**Values**.

The **Pivot Table **was inserted.

- To filter
**Florida**, click**Filter**. - Check
**Florida**. - Click
**OK**.

This is the output.

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

**Download Practice Workbook**

Download the practice workbook here.

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

Buenas tardes.

Al usar la función BUSCARV en excel, cuando quiero buscar ejemplo 23/03/2023 8:00 al buscar en la matriz solicitada me regresa 23/03/2023 7:00 las 2 tablas están en el mismo libro y tienen formato d/mm/yyyy h:mm

Hello

Leonardo,The issue you’re encountering with VLOOKUP returning the incorrect time (e.g., 8:00 returning as 7:00) may be related to how Excel handles date and time values. Double-check that both columns are formatted exactly the same, not just visually but also in terms of underlying data types (i.e., as date/time values).

Sometimes even slight formatting differences or time zone offsets can cause such issues. You might also try using TEXT functions to standardize the format.

If VLOOKUP is fetching a time that’s off by an hour, consider verifying any regional settings or daylight saving time adjustments.

Regards

ExcelDemy