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
When #N/A appears, many students immediately add IFERROR and show "0" – then the real problem (a space, text vs number) gets hidden and the report is wrong. Find the cause first: compare with =G2=Stores!A4 for TRUE/FALSE, check the length with LEN, and check the type with ISNUMBER. Once you know the cause, the fix is very simple.
Ravindra Bagale's Tip – मराठी
#N/A आला की बरेच students लगेच IFERROR लावून "0" दाखवतात – मग खरा problem (space, text-number) लपतो आणि report चुकतो. आधी कारण शोधा: =G2=Stores!A4 ने TRUE/FALSE compare करा, LEN ने length बघा, ISNUMBER ने type बघा. कारण कळलं की fix एकदम सोपा असतो.
Ravindra Bagale's Tip – हिंदी
#N/A आते ही बहुत से students तुरंत IFERROR लगाकर "0" दिखा देते हैं – फिर असली problem (space, text-number) छुप जाती है और report गलत हो जाती है. पहले वजह ढूँढो: =G2=Stores!A4 से TRUE/FALSE compare करो, LEN से length देखो, ISNUMBER से type देखो. वजह पता चल गई तो fix बहुत आसान होता है.
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!