VLOOKUP Not Working? Fix #N/A, #REF! and Wrong Results

When VLOOKUP fails, it is almost always one of five causes: invisible spaces around the key, text keys matched against numbers, a missing FALSE fourth argument, a column index that shifted when columns were inserted, or a deleted result column producing #REF!. Work through the checklist below in order.

Why Does VLOOKUP Return #N/A When the Value Is Clearly There?

Usually trailing or leading spaces — often from pasted or exported data — make "ACME " fail to match "ACME". Wrap the lookup value in TRIM, or clean the source column.

=VLOOKUP(TRIM(E2), A:C, 3, FALSE)

' Diagnose it first:
=LEN(E2)                    ' 5 chars for "ACME "?
=E2=INDEX(A:A, MATCH(TRIM(E2), A:A, 0))   ' FALSE = hidden difference

Are Your Keys Text or Numbers?

The number 1001 and the text "1001" look identical in a cell but never match each other — a classic result of IDs exported from other systems as text. Convert one side so both types agree.

' Lookup value is a number, table keys are text:
=VLOOKUP(TEXT(E2,"0"), A:C, 3, FALSE)

' Lookup value is text, table keys are numbers:
=VLOOKUP(--E2, A:C, 3, FALSE)

' Check which side is text (text returns TRUE):
=ISTEXT(A2)

Did You Forget the FALSE Fourth Argument?

Omitting the fourth argument defaults to TRUE (approximate match), which silently returns the wrong row unless your first column is sorted ascending. Always pass FALSE for exact match — wrong-but-plausible results are worse than errors.

=VLOOKUP(E2, A:C, 3)         ' risky: approximate match
=VLOOKUP(E2, A:C, 3, FALSE)  ' correct: exact match

Why Is VLOOKUP Pulling From the Wrong Column?

The column index is a hard-coded count from the left of the table, so inserting or deleting a column shifts your data but not the number — index 3 now points at a different column. Recount after any structure change, or switch to a formula that references columns directly.

This fragility is the main reason to prefer INDEX MATCH or XLOOKUP: both point at the return column itself, so inserting columns cannot break them.

What Causes #REF! in VLOOKUP?

#REF! means the column index is larger than the table has columns — either you typed 5 for a 3-column range, or someone deleted a column the formula depended on.

=VLOOKUP(E2, A:C, 5, FALSE)   ' #REF! — A:C is only 3 columns wide

Should You Wrap VLOOKUP in IFERROR?

Yes for display, no for debugging: IFERROR replaces #N/A with a friendly value, but it also hides genuine data problems, so add it only after the lookups are verified.

=IFERROR(VLOOKUP(E2, A:C, 3, FALSE), "Not found")

Common Pitfalls

• Non-breaking spaces (CHAR(160)): web-copied data resists TRIM. Use SUBSTITUTE(E2, CHAR(160), "").
• Unlocked table references: copying down without $A$2:$C$100 shifts the table off your data.
• Lookup column must be leftmost: VLOOKUP cannot look left. INDEX MATCH and XLOOKUP can.
• Duplicated keys: VLOOKUP returns only the first match; later rows are invisible to it.