VLOOKUP is the most-used lookup function in Google Sheets — and the most frustrating when it fails. The value is right there, and the formula still returns #N/A.

Good news: 90% of VLOOKUP failures come from five specific causes. Here they are, in order of how often I see them.

1. Text vs number mismatch (the most common)

Your lookup value is text ("123"), but the table contains numbers (123). They look identical to you; Sheets treats them as different species.

The fix: normalize both sides.

=VLOOKUP(TEXT(A2, "0"), B:C, 2, FALSE)

or clean your data with =VALUE(...) on the number column. Same problem in reverse: numbers stored as text by an import — =TEXT(...) handles that too.

2. Invisible characters (spaces, non-breaking spaces)

Imported data is full of ghosts: trailing spaces, non-breaking spaces (U+00A0), line breaks inside cells. "Product " ≠ "Product".

The fix: wrap the lookup value in TRIM — and if it’s still failing, check for non-breaking spaces with =CODE(RIGHT(A2,1)). A result of 160 means non-breaking space; SUBSTITUTE(A2, CHAR(160), "") kills it.

=VLOOKUP(TRIM(A2), B:C, 2, FALSE)

3. The lookup column isn’t the first column

VLOOKUP requires the lookup value to be in the first column of the range you give it. This one trips everyone eventually: your range is B:C, but the ID you’re matching is in column A.

The fix: either rearrange your range so the lookup column is first, or use INDEX/MATCH which has no such requirement:

=INDEX(C:C, MATCH(A2, B:B, 0))

4. Approximate match is on (FALSE vs TRUE)

The fourth argument: FALSE = exact match, TRUE = nearest match. Omit it and you get TRUE — which returns wrong results silently instead of an error, or #N/A when your data isn’t sorted.

The fix: always write FALSE explicitly. There is almost no good reason to use TRUE in real-world data.

5. The lookup value simply isn’t there (yet)

Sometimes the error is correct: the value isn’t in the range. Watch for:

  • The value is in a row below the range you selected.
  • You’re matching against a formula result that hasn’t recalculated (rare in Sheets, common in Excel imports).
  • The value exists but with different casing — VLOOKUP is case-insensitive, so that’s rarely it.

The fix: use =COUNTIF(B:B, A2) to check existence first. If it returns 0, the value genuinely isn’t in the column — the error is your data’s fault, not the formula’s.

The quick diagnostic order

When VLOOKUP fails, run this checklist top to bottom:

  1. TRIM both sides → still broken?
  2. Check types with =TYPE(A2) (2 = number, 1 = text) → mismatch?
  3. Is the lookup column first in the range? → if not, INDEX/MATCH.
  4. Is FALSE written explicitly? → if not, add it.
  5. COUNTIF existence check → not there at all?

One of those five is your answer. If your lookup runs right-to-left, SUMIF for conditional sums is the right tool — and for matching instead of summing, INDEX/MATCH is the modern alternative to VLOOKUP.

FAQ

Why is VLOOKUP returning #N/A when the value exists? Almost always one of: the lookup value is text but the table contains numbers (or vice versa), there are invisible spaces or non-breaking characters, the lookup column isn’t the first column of the range, or the value is in the wrong row (VLOOKUP searches column 1 from top to bottom and returns the FIRST match).

Does VLOOKUP work left to right only? Yes. VLOOKUP can only look to the RIGHT of the lookup column. If your result column is to the left, use INDEX/MATCH or XLOOKUP instead.

Can VLOOKUP handle spaces or formatting differences? No automatically. Use TRIM to remove spaces and TEXT or VALUE to normalize number/text types before matching.