4.10 Common Lookup Errors and Fixes
| Symptom | Cause | Fix |
|---|---|---|
| #N/A though the value is visible | Extra spaces or CHAR(160) | TRIM, SUBSTITUTE(…,CHAR(160),"") on both sides |
| #N/A for numeric IDs | One side is number, other is text (1001 vs '1001) |
Convert with VALUE() or TEXT(), or Text to Columns (Module 5) |
| Wrong value, no error | Approximate match on unsorted data / missing FALSE | Use exact match |
| Wrong column after edits | Hard-coded col_index | MATCH / XLOOKUP |
| #REF! | col_index larger than table width | Fix index or range |
| Works on first row only | Table range not locked with $ |
$A$2:$F$8 or a Table |
| Returns first match only | Duplicates in the lookup column | Remove duplicates or use FILTER (Module 9) |
| #VALUE! in XLOOKUP | Lookup and return arrays of different sizes | Same rows |
| #NAME? | XLOOKUP in Excel 2019 or older | Use INDEX + MATCH |
| Slow workbook | Thousands of full-column lookups | Use exact ranges/Tables, reuse one MATCH, or Power Query merge (Module 10) |
Worked example – checking why a lookup fails. Order store ID BLK-NSK-01 returns #N/A. =LEN(G2) gives 11, but BLK-NSK-01 has 10 characters – a trailing space. =XLOOKUP(TRIM(G2),Stores!$A$2:$A$8,Stores!$C$2:$C$8) returns Nashik.
Ravindra Bagale's Tip
#N/A aala ki khup students lagech IFERROR laavun "0" dakhavtat – mag khara problem (space, text-number) lapto aani report chukto. Aadhi karan shodha: =G2=Stores!A4 TRUE/FALSE ne compare kara, LEN ne length bagha, ISNUMBER ne type bagha. Karan kalla ki fix ekdum simple asto.
Practice task
Create five lookup failures (trailing space, text vs number, missing FALSE on unsorted data, unlocked range, wrong column index) and fix each one.
Thodkyaat sangaycha tar (quick recap)
VLOOKUP la FALSE aani $ visru naka, col_index cha trap lakshat theva; INDEX + MATCH sagalya versions madhe flexible aahe; XLOOKUP (365/2021+) sagalyat sopa – if_not_found, match/search modes aani multiple columns; slabs sathi lower limit + approximate match; #N/A aala tar aadhi karan shodha. Aata pudhe jaauya – data cleaning A to Z!