Formula guide · Excel and Google Sheets · Updated May 13, 2026
VLOOKUP Returns Wrong Value
Fix VLOOKUP returning the wrong result because of approximate match, duplicate keys, or shifted column numbers.
Quick Answer
If VLOOKUP returns the wrong value, make the final argument FALSE and check whether the return column number still points to the right column.
Copyable Formula
=VLOOKUP(E2,A2:D100,4,FALSE)Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)Excel and Google Sheets
Worked Example
| Cause | Fix |
|---|---|
| Approximate match | Use FALSE |
| Duplicate lookup keys | Remove or handle duplicates |
| Inserted column | Update col_index_num |
Result: Exact match plus a verified column number removes most wrong-result VLOOKUP problems.
Steps
- Check whether the final argument is FALSE.
- Count the return column from the left edge of the selected table.
- Search for duplicate lookup values in the first column.
- Consider XLOOKUP if the return column often moves.
Common Mistakes
- Leaving range_lookup blank and getting approximate matches.
- Counting the return column from the worksheet instead of the table range.
- Assuming duplicate keys are harmless in lookup tables.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.