
How to Fix Google Sheets #N/A 'Did Not Find Value' in VLOOKUP

Always set the 4th argument of VLOOKUP to FALSE or 0 for exact matching: =VLOOKUP(A2, Sheet2!A:C, 3, FALSE). If it still fails, wrap the lookup key in TRIM() to eliminate invisible trailing spaces.
=VLOOKUP(TRIM(A2), Sheet2!A:C, 3, FALSE)Why Does This Error Happen?
By default, VLOOKUP assumes the 4th parameter is TRUE (approximate match), requiring sorted data. Furthermore, Google Sheets treats '1024' as a number differently from '1024' stored as a string.
Step-by-Step Diagnostic & Resolution Walkthrough
1Step 1: Enforce Exact Matching Parameter
Verify that the final argument in your formula is FALSE or 0. Without this, VLOOKUP will search for approximate matches and return #N/A or false data.
=VLOOKUP(A2, Range, 2, FALSE)2Step 2: Clean Trailing Whitespaces with TRIM
Imported CSV files often introduce hidden spaces at the end of names or IDs. Wrap your lookup value or target range in TRIM.
=VLOOKUP(TRIM(A2), Range, 2, FALSE)3Step 3: Harmonize Data Types (Text vs Number)
If your lookup key is a number formatted as text, multiply by 1 or use VALUE() to match numeric indices in the lookup table.
=VLOOKUP(VALUE(A2), Range, 2, FALSE)Troubleshooting Matrix & Configuration Rules
| Symptom | Underlying Cause | Verified Formula Fix |
|---|---|---|
| #N/A on valid match | Approximate match default | Append , FALSE or , 0 at the end |
| #N/A on copy-pasted IDs | Trailing whitespace | =VLOOKUP(TRIM(key), table, col, 0) |
| #REF! in VLOOKUP | Index exceeds column width | Ensure column index <= lookup range width |
← Swipe horizontally to view full table →
Still Not Working? Check These Edge Cases
Fix: VLOOKUP only searches left-to-right. Upgrade to XLOOKUP: =XLOOKUP(A2, Sheet2!C:C, Sheet2!A:A).
Fix: Switch to INDEX & MATCH: =INDEX(C:C, MATCH(TRUE, INDEX(A:A=A2, 0), 0)).
Frequently Asked Questions
Is XLOOKUP better than VLOOKUP in Google Sheets?
Yes. XLOOKUP defaults to exact match, allows leftward searches, and doesn't break when columns are inserted or deleted.