Why is the vlookup returning wrong value

Answer

VLOOKUP can return the wrong value due to several common issues in Excel. Understanding these issues helps in correcting formulas and getting accurate results.

1. Incorrect Range Lookup Setting

VLOOKUP has an optional fourth argument called range_lookup.

• TRUE or omitted: Excel looks for an approximate match. The data must be sorted in ascending order for correct results. If it is unsorted, VLOOKUP may return an incorrect value.

• FALSE: Excel looks for an exact match. If the exact value does not exist, it returns #N/A.

Example:

=VLOOKUP(105, A2:B10, 2, FALSE)

This formula searches for 105 exactly in column A and returns the corresponding value from column B. If 105 does not exist, it returns #N/A.

2. Data Type Mismatch

VLOOKUP can return wrong values if the lookup value and the data in the table have different data types.

• Numbers stored as text will not match numeric values.

• Text with extra spaces or hidden characters will prevent a match.

Solution:

1. Use TRIM() to remove extra spaces.

2. Use VALUE() to convert text numbers to numeric format.

Example:

=VLOOKUP(VALUE("105"), A2:B10, 2, FALSE)

3. Column Index Number Error

VLOOKUP retrieves data from a column based on the col_index_num argument.

• If the column number exceeds the range of your table array, Excel returns #REF!.

• If the column number points to the wrong column, Excel returns the wrong value.

Example:

=VLOOKUP(105, A2:C10, 3, FALSE)

This retrieves the value from the 3rd column of the range A2:C10. Using 2 instead would return the value from the second column.

4. Duplicate Values in Lookup Column

VLOOKUP returns the first match it finds.

• If the lookup column has duplicates, Excel ignores the other occurrences.

• This can appear as though it is returning a “wrong” value.

Solution: Ensure the lookup column has unique values or use other functions like INDEX and MATCH for more control.

5. Hidden Characters or Formatting Issues

Data imported from other software may include:

• Non-breaking spaces

• Invisible characters

• Leading or trailing spaces

These prevent exact matches even if the values appear identical.

Solution:

=TRIM(CLEAN(A2))

Use this in a helper column to clean the data before using VLOOKUP.

6. Merged Cells

VLOOKUP cannot correctly search in ranges with merged cells.

• Merged cells cause Excel to treat the range inconsistently.

• Unmerge cells to fix the lookup.

7. Approximate Match Used by Mistake

Using TRUE or omitting range_lookup when an exact match is needed can produce unexpected results.

Example:

=VLOOKUP(107, A2:B10, 2, TRUE)

If 107 is not found, Excel returns the largest value less than 107, which may appear wrong.

Quick Checklist to Fix Wrong VLOOKUP Values

1. Check range_lookup argument (TRUE for approximate, FALSE for exact).

2. Ensure lookup value matches the data type in the table.

3. Verify the column index number points to the correct column.

4. Remove extra spaces or hidden characters using TRIM() or CLEAN().

5. Avoid duplicates in the lookup column when an exact match is required.

6. Unmerge any merged cells in the lookup range.

7. Sort data only if using approximate match (TRUE).

Following these steps resolves most issues with VLOOKUP returning wrong values.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report