Why does VLOOKUP return the wrong value in Excel?
Formula (Excel and Google Sheets)
=VLOOKUP(F1,A2:C9,3,FALSE)The fourth argument is missing. Without FALSE, VLOOKUP does an approximate match on unsorted data: =VLOOKUP(F1,A2:C9,3,FALSE).
- Result on the example
- $449.00 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
Leaving out the last argument, or setting it to TRUE, tells VLOOKUP the first column is sorted, and it returns the closest match it lands on. On an unsorted list that is another product's price, with no error to warn you. FALSE (or 0) forces an exact match, so P-104 returns 449.
=VLOOKUP(F1,A2:C9,3,FALSE)Result449
F1"P-104"The value in F1.
A2:C98 rows × 3 cols: "P-103", "Monitor arm", $89.50, "P-101", …A range: 8 rows × 3 columns.
VLOOKUP(F1,A2:C9,3,FALSE)449VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.- lookup_value
F1"P-104" - table_array
A2:C98 rows × 3 cols: "P-103", "Monitor arm", $89.50, "P-101", … - col_index_num
3 - range_lookup
FALSE
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =VLOOKUP(F1,A2:C9,3,FALSE). We ran it in Google Sheets mode on the same data and it returns $449.00.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(F1,A2:A9,C2:C9) | $449.00 | XLOOKUP matches exactly unless you ask for something else. |
| =INDEX(C2:C9,MATCH(F1,A2:A9,0)) | $449.00 | INDEX/MATCH with 0 for an exact match, in any version. |
Common mistakes
- Approximate match is right only for sorted bands, like tax brackets or commission tiers. For IDs, names and codes, always use FALSE.
- Duplicates in the first column return the first match, which can also look like a wrong value.
- A column number pointing at the wrong column returns a real value from the wrong field. Count from the table's first column.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does VLOOKUP return the wrong value
F2, return the price of the SKU in F1 from the unsorted list in A2:C9.Questions
Is 0 the same as FALSE?
Yes. =VLOOKUP(F1,A2:C9,3,0) is an exact match too.
How can I see which row VLOOKUP matched?
Return the first column: =VLOOKUP(F1,A2:C9,1) shows the SKU the approximate match landed on.
Why does VLOOKUP return a price for a SKU that doesn't exist?
With an approximate match, a missing SKU returns a nearby row instead of #N/A. With FALSE it returns #N/A, which is the honest answer.
Why did my VLOOKUP break after I inserted a column?
The column number stayed the same while the columns moved. Use MATCH on the header, or XLOOKUP.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.