Error fix · Excel & Google Sheets

VLOOKUP returns #N/A — 5 causes and how to fix each.

Q: How do I make VLOOKUP ignore trailing spaces?

Use =VLOOKUP(TRIM(A2), table, col, FALSE). If the source column also has trailing spaces, you need to clean it too — either with a helper column =TRIM(original) or by using Find & Replace to strip spaces. VLOOKUP itself has no 'ignore whitespace' switch.

Q: Can VLOOKUP return empty or zero instead of #N/A?

Wrap the call: =IFNA(VLOOKUP(...), "") returns an empty string on #N/A and passes through any other error. =IFERROR(VLOOKUP(...), 0) returns 0 on any error, but also hides real bugs like #REF! or #VALUE! — prefer IFNA when you only want to catch not-found cases.

Q: What's the difference between #N/A and #REF! in VLOOKUP?

#N/A means the lookup_value was not found in the first column of table_array — a data-level problem. #REF! means col_index_num points past the end of table_array (e.g. you asked for column 5 in a 3-column range) — a formula-level problem. The fixes are completely different: #N/A means check your data; #REF! means fix your range or col_index.

Q: Is VLOOKUP case-sensitive?

No. "apple" and "APPLE" match the same row. So if you're getting #N/A between lowercase and uppercase versions, case is NOT the cause — it's almost always whitespace or a type mismatch. For genuinely case-sensitive lookup, combine INDEX/MATCH with EXACT: =INDEX(B:B, MATCH(TRUE, EXACT(A:A, "Apple"), 0)).

Last updated: April 2026

#N/A means VLOOKUP couldn't find lookup_value in the first column of table_array. Not a bug — a “not found” signal. In 80% of real cases the value is there, but a hidden difference (whitespace, type, wrong column) is breaking the match. Below: a quick visual, a symptom table, then each cause with an interactive demo.

=VLOOKUP("Apple", A2:B5, 2, FALSE)

	A — Product	B — Price
2	Apple·TRAILING SPACE	$1.20
3	·AppleLEADING SPACE	$0.95
4	Apple⎵NBSP	$1.40
5	ApplesTYPO	$1.50
6
7	VLOOKUP RESULT	#N/A

Symptom	Likely cause	Quick fix
Values look identical but don't match	Hidden whitespace	=VLOOKUP(TRIM(…), …) →
Numbers appear but no match	Stored as text	=VLOOKUP(VALUE(A2), …) →
Value is in column C, not column A	Wrong column searched	Use XLOOKUP / INDEX+MATCH →
Some rows match, some don't	Approximate match + unsorted data	Pass FALSE as 4th arg →
Single row missing	Typo or data drift	=COUNTIF(A:A, lookup) →

01 · Most common cause

Cause 1: Hidden whitespace (~60% of cases)

Trailing spaces, leading spaces, non-breaking spaces, tabs. The values look identical side-by-side, but byte-for-byte they're not.

This is the #1 reason VLOOKUP not working with #N/A. Whitespace sneaks in from CSV exports, copy-pastes from PDFs or web pages, and system-generated reports. "Apple" and "Apple " (trailing space) are completely different strings to VLOOKUP.

FixWrap both the lookup value and the source column in TRIM():
=VLOOKUP(TRIM(A2), TRIM(data_range), 2, FALSE)
For non-breaking spaces (char 160) that TRIM misses: =VLOOKUP(TRIM(SUBSTITUTE(A2, CHAR(160), " ")), …).

02 · Silent killer

Cause 2: Numbers stored as text

"42" (text) and 42 (number) are not equal to VLOOKUP. The display looks identical; the underlying type differs.

Telltale sign: a little green triangle in the top-left of the cell, or the number left-aligned (text aligns left, numbers align right by default). Often caused by CSV imports, barcode scanners, or data pulled from web APIs.

FixConvert one side to match:
=VLOOKUP(VALUE(A2), table, 2, FALSE) — if A2 is text-that-looks-like-a-number
=VLOOKUP(A2 & "", table, 2, FALSE) — if the table column is text but A2 is a real number
Or permanently: select column → Data → Text to Columns → Finish to force re-parsing.

03 · Structural problem

Cause 3: Looking in the wrong column

VLOOKUP only searches the first column of table_array. If your lookup value lives in column C, VLOOKUP can't find it — even though it's right there.

This is a design limitation of VLOOKUP, not a bug you can fix inside VLOOKUP. The fix is to either re-arrange your range so the search column is first, or switch to a function that supports leftward lookup.

Fix — use XLOOKUP (modern Excel, Sheets)=XLOOKUP(lookup_value, C:C, A:A) — search column C, return from column A.

Fix — use INDEX + MATCH (any version)=INDEX(A:A, MATCH(lookup_value, C:C, 0))

04 · The TRUE trap

Cause 4: Approximate match on unsorted data

If the 4th argument is TRUE (or omitted), VLOOKUP assumes the first column is sorted ascending. If it isn't, you get #N/A — or worse, wrong values that look correct.

Omitting the 4th argument defaults it to TRUE — a historical footgun. TRUE mode is only safe for genuinely sorted numeric ranges (tax brackets, tier thresholds). For any exact-match lookup, always pass FALSE explicitly.

FixMake FALSE the default muscle memory for the 4th arg:
=VLOOKUP(A2, table, 2, FALSE)
In Google Sheets the 4th arg is called [is_sorted] and defaults to TRUE — same trap.

05 · Rare but real

Cause 5: Typo or missing data

Sometimes the value really isn't there. A typo, a deleted row, a source system that dropped a record.

Verify existence before debugging fancier causes: =COUNTIF(A:A, "*" & lookup_value & "*") — wrapping the value in wildcards also catches partial matches, so you can see if a near-miss is the culprit.

FixIf COUNTIF returns 0, the value genuinely isn't in the range. Check upstream: did the source export complete? Did someone filter or sort-and-cut the data?

06 · When you can't fix the data

Wrap in IFNA for a clean fallback

If not-found is a legitimate state (e.g. optional lookups in a dashboard), suppress the #N/A with a fallback value.

Don't reach for IFERROR as the default — it silently hides every error, including real bugs like #REF! or #VALUE!. Use IFNA when you only want to catch the “not found” case.

Pattern=IFNA(VLOOKUP(A2, table, 2, FALSE), "Not found")
Or return empty: =IFNA(VLOOKUP(…), "")
Or zero for numeric sums: =IFNA(VLOOKUP(…), 0)

Related troubleshooting

Other VLOOKUP errors and the main reference page.

07 · Quick answers

FAQ

7.01Why does VLOOKUP return #N/A when the value is clearly there?▸

The value looks identical but has a hidden difference — most often trailing whitespace from a CSV import or copy-paste, or a number stored as text on one side and a real number on the other. VLOOKUP's FALSE-mode match is strict byte-for-byte (case-insensitive but otherwise exact). Wrap the lookup in TRIM() to rule out whitespace, or in VALUE() to align types.

7.02How do I make VLOOKUP ignore trailing spaces?▸

Use =VLOOKUP(TRIM(A2), table, col, FALSE). If the source column also has trailing spaces, you need to clean it too — either with a helper column =TRIM(original) or via Find & Replace. VLOOKUP has no “ignore whitespace” switch.

7.03Can VLOOKUP return empty or zero instead of #N/A?▸

Wrap the call: =IFNA(VLOOKUP(…), "") returns an empty string on #N/A. =IFERROR(VLOOKUP(…), 0) returns 0 on any error, but also hides real bugs like #REF! or #VALUE! — prefer IFNA when you only want to catch not-found cases.

7.04What's the difference between #N/A and #REF! in VLOOKUP?▸

#N/A means lookup_value was not found in the first column of table_array — a data-level problem. #REF! means col_index_num points past the end of the range (e.g. you asked for column 5 in a 3-column range) — a formula-level problem. Fix #N/A by checking your data; fix #REF! by fixing your range or index.

7.05Is VLOOKUP case-sensitive?▸

No. "apple" and "APPLE" match the same row. So if you're getting #N/A between case variants, case is not the cause — it's almost always whitespace or a type mismatch. For genuinely case-sensitive lookup, combine INDEX/MATCH with EXACT: =INDEX(B:B, MATCH(TRUE, EXACT(A:A, "Apple"), 0)).