Google Sheets VLOOKUP Exact Match Issues & How To Fix Them

Your VLOOKUP with exact match (FALSE) is returning #N/A or incorrect results, even though the value clearly exists.
This typically happens when data looks identical but isn’t actually matching due to formatting or structure issues.

This guide fixes it step by step.

Why the Issue Happens

• Text vs number mismatch (100 vs "100")
• Extra spaces or hidden characters
• Lookup value not in the first column
• Case differences (in rare exact checks)
• Leading/trailing spaces in dataset
• Duplicate values causing confusion
• Incorrect range or column index
• Using approximate match instead of exact

Step-by-Step Fixes

Step 1: Use Correct Exact Match Formula

Correct syntax:

=VLOOKUP(A2, A:B, 2, FALSE)

• FALSE forces exact match
• Without it, results may be wrong

Always include FALSE.

Step 2: Confirm Lookup Value Exists

Test:

=COUNTIF(A:A, A2)

• 0 → value not found
• >0 → value exists

If 0, issue is data mismatch.

Step 3: Fix Text vs Number Mismatch

Example:

• Lookup value = 100
• Table value = "100"

These won’t match.

Fix:

=VLOOKUP(VALUE(A2), A:B, 2, FALSE)

or:

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

Step 4: Remove Extra Spaces

Hidden spaces break exact match.

Fix:

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

Clean dataset:

=ARRAYFORMULA(TRIM(A:A))

Step 5: Remove Hidden Characters

Imported data often contains invisible characters.

Fix:

=VLOOKUP(TRIM(CLEAN(A2)), A:B, 2, FALSE)

Step 6: Ensure Lookup Column is First

VLOOKUP only searches the first column.

Wrong:

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

Correct:

=VLOOKUP(A2, A:C, 3, FALSE)

Step 7: Check Column Index

If index is wrong, result may fail.

Example:

=VLOOKUP(A2, A:B, 3, FALSE)  ❌

Correct:

=VLOOKUP(A2, A:B, 2, FALSE)

Step 8: Handle Duplicate Values

VLOOKUP returns first match only.

If duplicates exist:

• You may get unexpected results

Alternative:

=FILTER(B:B, A:A=A2)

Returns all matches.

Step 9: Debug with EXACT

Check if values truly match:

=EXACT(A2, A3)

If FALSE → hidden mismatch exists.

Step 10: Use IFERROR for Clean Output

Instead of showing #N/A:

=IFERROR(VLOOKUP(A2, A:B, 2, FALSE), "Not Found")

Common Mistakes

• Forgetting FALSE for exact match
• Ignoring text vs number mismatch
• Not cleaning spaces or hidden characters
• Using wrong lookup range
• Expecting VLOOKUP to search left
• Not checking if value actually exists
• Misinterpreting duplicates

Pro Tips / Better Alternatives

Use XLOOKUP (Better Alternative)

=XLOOKUP(A2, A:A, B:B)

• No column index
• Works both directions
• Cleaner and more reliable

Use INDEX + MATCH

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

More flexible and robust.

Use FILTER for Multiple Matches

=FILTER(B:B, A:A=A2)

Returns all results instead of just one.

Clean Data Before Lookup

Use:

=TRIM()
=CLEAN()
=VALUE()

Clean data = accurate matches.

Avoid Full Column References in Large Sheets

Instead of:

=VLOOKUP(A2, A:B, 2, FALSE)

Use:

=VLOOKUP(A2, A2:B1000, 2, FALSE)

Improves performance.

Bottom Line

If VLOOKUP exact match isn’t working, fix in this order:

1. Ensure FALSE is used
2. Check if value actually exists
3. Fix text vs number mismatch
4. Remove spaces and hidden characters
5. Verify lookup column and range
6. Handle duplicates if needed

Most issues come from data inconsistencies, not the formula itself.
Clean the data, and exact match will work reliably