VLOOKUP Returns the Wrong Value

Excel & Google Sheets

How it works

A wrong value is worse than an error code, because nothing flags it — the report just ships with bad data. The top cause is omitting the fourth argument: without FALSE, VLOOKUP does an approximate match, which assumes the lookup column is sorted and happily returns the nearest smaller value from unsorted data. Always write FALSE (or 0) for exact match. Second, the column number counts positions inside the table range, so inserting or deleting a column in the lookup table silently shifts what column 3 means — your formula now returns the neighboring column. Third, when the lookup column contains duplicate keys, VLOOKUP returns only the first match and ignores the rest, which looks correct until you compare against the second record. Check for duplicates with =COUNTIF(Sheet2!A:A,A2) — anything above 1 means your "match" is really a coin flip that always lands on the first row.

A2

The value to look up — an ID, email, or name.

Sheet2!$A:$C

The lookup table, anchored with $ so the range doesn't drift when the formula is copied down.

3

Return the 3rd column of the table. Recount this after anyone inserts or deletes columns in the table.

FALSE

Exact match. Omit it and VLOOKUP switches to approximate match, returning near-misses from unsorted data.

When to use it

Audit these three causes whenever lookup results fail a spot check, after columns are inserted into the lookup table, or when the lookup column may contain duplicate IDs.

Common mistakes

• ✕Leaving off the FALSE argument.

  ✓=VLOOKUP(A2,$A:$C,3) approximate-matches and returns the nearest smaller value on unsorted data. Add FALSE: =VLOOKUP(A2,$A:$C,3,FALSE).

• ✕Trusting the column number after the table changed.

  ✓Inserting a column inside $A:$C shifts what position 3 holds. Recount, or switch to XLOOKUP, which names the return column and can't shift.

• ✕Assuming duplicates are handled.

  ✓VLOOKUP only ever returns the first match. Test with =COUNTIF(Sheet2!A:A,A2)>1, then dedupe the table or make keys unique before trusting results.