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.

90% of VLOOKUP failures come from five causes. Here they are, roughly in the order they occur.

The five errors at a glance

What you see Usual cause The fix
#N/A on a value you can see Text on one side, numbers on the other Wrap the lookup value in TEXT() or the column in VALUE()
#N/A only for data you pasted in Trailing spaces or non-breaking spaces (U+00A0) TRIM() the lookup value, strip U+00A0 with SUBSTITUTE()
#N/A for every row The lookup column is not the first column of the range Move the column or switch to INDEX + MATCH
A plausible but wrong value The fourth argument is missing, so the match is approximate Write FALSE as the fourth argument
#N/A and the value is genuinely absent The value sits below the range, or in another sheet Confirm with COUNTIF before changing the formula

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, and =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, and SUBSTITUTE(A2, CHAR(160), "") removes 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 value exists more than once. VLOOKUP walks column 1 from the top and returns the first hit, so a duplicated key gives a correct-looking answer for the wrong row. =COUNTIF(B:B, A2) tells you how many matches exist, and anything above 1 means your result depends on row order.

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.

A worked example: the same formula, two data types

Pasted data often arrives as text while the table is keyed on numbers:

A2:      "1042"        B2:C4:   1042 | Widget      (number)
sold as text                     1043 | Gadget
                                 1044 | Gasket

=VLOOKUP(A2, B:C, 2, FALSE)            returns #N/A    the text "1042" is not the number 1042
=VLOOKUP(VALUE(A2), B:C, 2, FALSE)     returns Widget  converted on the lookup side
=VLOOKUP(TEXT(A2,"0"), B:C, 2, FALSE)  returns #N/A    the table side is still a number

The third line is the trap here: converting one side is not enough. Convert the side that has the wrong type, and confirm with =TYPE(A2) (1 means text, 2 means number) before you rewrite the formula again.

Which lookup function should you use?

Function Can it look left? Survives an inserted column? Missing value
VLOOKUP No No, the column index shifts #N/A
INDEX + MATCH Yes Yes #N/A
XLOOKUP Yes Yes #N/A

INDEX + MATCH is the safer upgrade when your layout moves, because it matches on the column itself.

Turning #N/A into something readable

A bare #N/A makes a dashboard look broken. Wrap the lookup in IFNA to return a message instead:

=IFNA(VLOOKUP(A2, B:C, 2, FALSE), "not found")

Use IFNA rather than IFERROR here. IFERROR also hides genuine problems such as a wrong range reference or a division error, so it can turn a real bug into a silent blank.

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, use 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 you need to add up matching rows instead of looking one up, SUMIF handles conditional sums. Comparing the two spreadsheet apps is a separate question, and Excel vs Google Sheets covers where their lookup behaviour differs. If a column of text needs splitting before you can match on it at all, splitting text into columns is the step to do first.

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.

Why does VLOOKUP return a wrong value instead of an error? Because the range contains more than one match and VLOOKUP stops at the first one, or because the fourth argument is left out and Google Sheets falls back to an approximate match. Write FALSE explicitly and check for duplicates with COUNTIF on the lookup column.

Is XLOOKUP better than VLOOKUP in Google Sheets? For most cases yes. XLOOKUP can look in either direction, returns a clean #N/A when a value is missing, defaults to an exact match, and survives column insertions. VLOOKUP is still fine when your lookup column sits on the left and your range is fixed.

Sources

Warning

VLOOKUP always looks in the first column of the range you give it. If you copy the formula to the right, it keeps returning that same first column. Lock the range with $ (for example $A$2:$C$50) or switch to INDEX + MATCH.