If your VLOOKUP in Google Sheets is returning #N/A, #REF!, or incorrect results, the issue is usually in your formula setup, data format, or lookup structure.
This guide shows exactly how to fix VLOOKUP errors step-by-step.
Google Sheets VLOOKUP Not Working
Intro
Your VLOOKUP in Google Sheets is returning #N/A, #REF!, or incorrect values.
This usually means something is wrong with your formula setup, data format, or lookup structure.
This guide fixes it step by step.
Why the Issue Happens
- Lookup value doesn’t exist in the first column
- Exact match not enforced (
FALSEmissing) - Text vs number mismatch
- Extra spaces or hidden characters
- Wrong column index
- Lookup range shifting when dragged
- Data not sorted (when using approximate match)
- Duplicate values in lookup column
Step-by-Step Fixes
Step 1: Check Formula Structure
VLOOKUP must follow this format:
=VLOOKUP(search_key, range, index, [is_sorted])Example:
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)If your structure is wrong, fix this first.
Step 2: Force Exact Match
If you don’t use exact match, results can be wrong.
Fix:
=VLOOKUP(A2, A:B, 2, FALSE)Never leave the last argument blank unless you want approximate matching.
Step 3: Ensure Lookup Column is FirstVLOOKUP only searches in the first column of the range.
Wrong:
=VLOOKUP(A2, B:C, 2, FALSE)Correct:
=VLOOKUP(A2, A:C, 3, FALSE)If your lookup value isn’t in the first column, VLOOKUP won’t work.
Step 4: Fix #N/A (Value Not Found)
If you see #N/A, the value isn’t matching.
Check:
- Is the value actually present?
- Are there formatting differences?
Use this to handle errors:
=IFERROR(VLOOKUP(A2, A:B, 2, FALSE), "Not Found")Step 5: Fix Text vs Number Mismatch
This is a silent but common issue.
Example:
- A2 =
123(number) - Table =
"123"(text)
Fix by converting:
=VLOOKUP(VALUE(A2), A:B, 2, FALSE)or
=VLOOKUP(TEXT(A2, "0"), A:B, 2, FALSE)Step 6: Remove Extra Spaces
Hidden spaces break matches.
Fix:
=VLOOKUP(TRIM(A2), A:B, 2, FALSE)To clean a full column:
=ARRAYFORMULA(TRIM(A:A))Step 7: Fix Column Index Errors (#REF!)
If index exceeds range, you get #REF!.
Wrong:
=VLOOKUP(A2, A:B, 3, FALSE)Correct:
=VLOOKUP(A2, A:B, 2, FALSE)Make sure index matches the column count in your range.
Step 8: Lock the Range
If results change when dragging formulas, your range is moving.
Fix:
=VLOOKUP(A2, $A$2:$B$100, 2, FALSE)Use $ to lock the range.
Step 9: Handle Approximate Match Properly
If using:
=VLOOKUP(A2, A:B, 2, TRUE)Then:
- First column must be sorted ascending
If not, results will be incorrect.
If unsure, always use:
FALSEStep 10: Handle Duplicates
VLOOKUP returns only the first match.
If duplicates exist, results may seem wrong.
Alternative:
=FILTER(B:B, A:A=A2)This returns all matching values.
Common Mistakes
- Leaving last argument blank (defaults to TRUE)
- Using VLOOKUP when lookup column isn’t first
- Ignoring text vs number mismatch
- Copy-pasting data with hidden spaces
- Not locking ranges
- Using wrong column index
- Expecting multiple results from VLOOKUP
- Not checking if value actually exists
Pro Tips / Better Alternatives
Use XLOOKUP (Cleaner and More Flexible)
=XLOOKUP(A2, A:A, B:B)- No column index needed
- Works left and right
- Cleaner syntax
Use INDEX + MATCH (More Control)
=INDEX(B:B, MATCH(A2, A:A, 0))- More robust than VLOOKUP
- Handles dynamic structures better
Use FILTER for Multiple Matches
=FILTER(B:B, A:A=A2)Returns all matching results instead of just one.
Debug with EXACT
Check if two values truly match:
=EXACT(A2, A3)Returns TRUE or FALSE.
Clean Data Before Using VLOOKUP
Use:
=TRIM()
=CLEAN()Dirty data is a major cause of lookup failures.
Bottom Line
If VLOOKUP isn’t working, fix in this order:
- Use
FALSEfor exact match - Ensure lookup column is first
- Match data types (text vs number)
- Remove extra spaces
- Check column index
- Lock your range
Once these are fixed, your VLOOKUP will work consistently and your model will stay reliable.
how about this