How to Fix VLOOKUP Returning Wrong Value in Excel

VLOOKUP returns wrong values often because the range_lookup argument is set to TRUE or omitted, causing approximate matches. To fix this, set range_lookup to FALSE for exact matches, like =VLOOKUP(value, table, col_index, FALSE).

Why This Happens

VLOOKUP returns wrong values mainly because it defaults to an approximate match when the range_lookup argument is missing or set to TRUE. This means Excel looks for the closest match, not the exact one, which can cause unexpected results if your data isn't sorted. Another common cause is referencing the wrong column index or having extra spaces in lookup values.

excel

=VLOOKUP(A2, B2:D10, 2)

Output

Returns wrong or unexpected value because approximate match is used by default

The Fix

To fix the wrong value issue, always add FALSE as the last argument in your VLOOKUP formula. This forces Excel to find an exact match. Also, check that the column index number is correct and that your lookup values have no extra spaces.

excel

=VLOOKUP(A2, B2:D10, 2, FALSE)

Output

Returns the exact matching value from the second column of the table

Prevention

To avoid VLOOKUP errors in the future, always:

• Use FALSE for exact matches unless you specifically want approximate matches.
• Ensure your lookup column is clean and free of extra spaces or hidden characters.
• Double-check the column index number to match the correct data column.
• Consider using TRIM() on lookup values to remove spaces.

Related Errors

Other common VLOOKUP errors include:

• #N/A: Lookup value not found. Fix by checking spelling or data presence.
• #REF!: Column index is out of range. Fix by adjusting the column number.
• Wrong data type: Lookup value and table data types differ (e.g., number vs text). Fix by converting types.

Key Takeaways

Always use FALSE as the last argument in VLOOKUP for exact matches.

Check that the column index number matches the data you want to retrieve.

Clean lookup values to remove extra spaces or hidden characters.

Understand that omitting the range_lookup argument defaults to approximate match.

Verify data types match between lookup value and table data.