# Why does VLOOKUP return the wrong value in Excel?

The fourth argument is missing. Without FALSE, VLOOKUP does an approximate match on unsorted data: =VLOOKUP(F1,A2:C9,3,FALSE).

**Excel:** `=VLOOKUP(F1,A2:C9,3,FALSE)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| SKU | Product | Price |  | SKU | P-104 |
| P-103 | Monitor arm | 89.5 |  | Price |  |
| P-101 | Desk lamp | 39.99 |  |  |  |
| P-106 | Webcam | 59 |  |  |  |
| P-108 | Laptop stand | 45 |  |  |  |
| P-104 | Standing desk | 449 |  |  |  |
| P-102 | Office chair | 249 |  |  |  |
| P-107 | Headset | 129 |  |  |  |
| P-105 | Keyboard | 74.95 |  |  |  |

Formula in F2: `=VLOOKUP(F1,A2:C9,3,FALSE)`

Result: $449.00

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

## Other ways

- `=XLOOKUP(F1,A2:A9,C2:C9)`: XLOOKUP matches exactly unless you ask for something else.
- `=INDEX(C2:C9,MATCH(F1,A2:A9,0))`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/vlookup-returns-wrong-value