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) |
About this data: Teaching sample for a fictional shop “FreshGo Mart”. Figures and names (e.g. Aamir Khan) are made up for this lesson on common lookup errors and fixes.
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.
In simple words (sopya bhashet)
Friends, #N/A is Excel telling you: "I could not find this value in the lookup column." To your eyes both values look the same, but for Excel they can be different: a hidden space at the end, a number on one side and text on the other, or the wrong range. So first look at it the way Excel does – compare with LEN, ISNUMBER and =. Once you find the cause, the fix takes two minutes.
मित्रांनो, #N/A म्हणजे Excel सांगतो – "मला ही व्हॅल्यू लुकअप कॉलममध्ये सापडली नाही." तुमच्या डोळ्यांना दोन्ही व्हॅल्यूज सारख्याच दिसतात, पण Excel साठी त्या वेगळ्या असू शकतात: शेवटी एक लपलेली स्पेस, एकीकडे नंबर आणि दुसरीकडे टेक्स्ट, किंवा चुकीची रेंज. म्हणून आधी Excel च्या नजरेतून बघा – LEN, ISNUMBER आणि = ने कम्पेअर करा. कारण सापडलं की फिक्स दोन मिनिटांचं काम आहे.
दोस्तों, #N/A का मतलब है Excel कह रहा है – "मुझे यह वैल्यू लुकअप कॉलम में नहीं मिली." आपकी आँखों को दोनों वैल्यूज़ एक जैसी दिखती हैं, लेकिन Excel के लिए वे अलग हो सकती हैं: आख़िर में एक छुपी हुई स्पेस, एक तरफ़ नंबर और दूसरी तरफ़ टेक्स्ट, या ग़लत रेंज. इसलिए पहले Excel की नज़र से देखो – LEN, ISNUMBER और = से कम्पेयर करो. कारण मिल गया तो फ़िक्स दो मिनट का काम है.
Step by step: a 4-check routine for any failing lookup
Suppose =XLOOKUP(A2, Master!$A$2:$A$50, Master!$C$2:$C$50) fails for one row and the matching value in the master is in cell Master!A7.
- Compare directly:
=A2=Master!A7. TRUE means they really are equal, so the problem is in the ranges or the match type. FALSE means the two values are different for Excel – go to check 2. - Compare lengths:
=LEN(A2)and=LEN(Master!A7). Different lengths mean hidden characters: a space (fix withTRIM) or a non-breaking spaceCHAR(160)(fix withSUBSTITUTE). - Compare types:
=ISNUMBER(A2)and=ISNUMBER(Master!A7). One TRUE and one FALSE means number vs text (fix withVALUEorTEXT). - Check the formula itself: is the range locked with
$, is the match exact (FALSE/0in VLOOKUP), does the range cover all rows of the master?
Only after the cause is fixed, wrap the formula in IFERROR or use XLOOKUP's if_not_found for values that are truly missing.
Worked example 1 – A Pune dairy (fictional): number vs text customer codes
A fictional Pune dairy keeps a customer master where Customer Code is a real number. The daily milk-route sheet is copied from a billing export where the codes came in as text.
| Route sheet A2 | Master A7 | =A2=Master!A7 |
=ISNUMBER(A2) |
=ISNUMBER(Master!A7) |
|---|---|---|---|---|
| 1001 (text, left-aligned, green triangle) | 1001 (number, right-aligned) | FALSE | FALSE | TRUE |
About this data: Demo table only — not real customer or company data. Built to practise common lookup errors and fixes.
The lookup =XLOOKUP(A2, Master!$A$2:$A$50, Master!$B$2:$B$50) returns #N/A. Two correct fixes:
- Convert inside the formula:
=XLOOKUP(VALUE(A2), Master!$A$2:$A$50, Master!$B$2:$B$50)→ Kulkarni Sweets. - Or convert the whole column once: select the route-sheet codes › the warning icon › Convert to Number (or Data › Text to Columns › Finish, Module 5).
Worked example 2 – Panchganga Gul Bhandar, Kolhapur (fictional): TRIM is not enough
The fictional Kolhapur jaggery shop copied its product list from its own web page into Excel. =VLOOKUP(A2, Products!$A$2:$C$40, 3, FALSE) fails for "Gul Cubes 5kg" even after TRIM.
| Check | Result | Meaning |
|---|---|---|
=LEN(A2) |
14 | but "Gul Cubes 5kg" has 13 characters |
=LEN(TRIM(A2)) |
14 | TRIM removed nothing |
=CODE(RIGHT(A2,1)) |
160 | the last character is a non-breaking space |
About this data: Made-up rows so you can copy the pattern for common lookup errors and fixes. Currencies may use ₹, £, € or $.
TRIM removes normal spaces (code 32) only. Text copied from web pages often carries non-breaking spaces (code 160). Fix: =VLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160)," ")), Products!$A$2:$C$40, 3, FALSE) – replace code 160 with a normal space, then TRIM.
Worked example 3 – Nashik Valley Grapes (fictional exporter): wrong rate, no error
A fictional Nashik grape exporter charges packing per box by slab. Someone typed the slab table in the wrong order:
| Min Boxes | Rate per box (₹) |
|---|---|
| 500 | 30 |
| 0 | 40 |
| 100 | 35 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
For an order of 250 boxes, =VLOOKUP(250, Slabs!$A$2:$B$4, 2, TRUE) expects a table sorted ascending. On this unsorted table the result cannot be trusted – it can return a wrong rate and no error appears. The correct rate is ₹35 (250 is in the 100–499 slab).
- Fix 1: sort the slab table ascending on Min Boxes (0, 100, 500); then VLOOKUP TRUE returns 35.
- Fix 2:
=XLOOKUP(250, Slabs!$A$2:$A$4, Slabs!$B$2:$B$4, , -1)– match mode −1 (exact or next smaller) looks through the list and does not depend on sort order, so it returns 35 even on the unsorted table.
Common mistake – the unlocked range that works only in the first rows
In Orders, P2 has =VLOOKUP(G2,Stores!A2:F8,3,FALSE) without $. When you fill it down, the range moves with the formula: in P5 it becomes Stores!A5:F11. Rows 2 to 4 of Stores are no longer inside the range, so an order for BLK-PUN-01 (row 2 of Stores) now returns #N/A in P5, while the same Store ID works in P2.
| Cell | Formula after fill down | Store ID in G | Result |
|---|---|---|---|
| P2 | =VLOOKUP(G2,Stores!A2:F8,3,FALSE) |
BLK-PUN-01 | Pune |
| P5 | =VLOOKUP(G5,Stores!A5:F11,3,FALSE) |
BLK-PUN-01 | #N/A |
About this data: Teaching sample for a fictional shop “FreshGo Mart”. Figures and names (e.g. Aamir Khan) are made up for this lesson on common lookup errors and fixes.
Fix: lock the range with F4 – Stores!$A$2:$F$8 – or convert Stores to an Excel Table (Module 6) so the reference never moves.
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.
Loading practice tasks...
- Spot the space (Easy): Count the characters in H2.
- Scheme: spot the space (Easy): Count the characters in Scheme!F2.
- Fix with TRIM (Medium): Find the City of H2. Use TRIM so it works.
- Fix #N/A with IFNA (Medium): Find the City of H3. Show "Not in master" if missing, using IFNA and VLOOKUP.
- Scheme: fix with TRIM (Medium): Find the Name of Scheme!F2. Use TRIM.
- Scheme: not registered (Medium): Find the Amount (column 4) of Scheme!F3. Show "Not registered" if missing. Use IFNA and VLOOKUP.
- Fix #REF! (Hard): =VLOOKUP("BLK-KOP-01",$A$2:$E$8,6,FALSE) gives #REF!. Fix it to return the City Manager.
- Wrong match type (Hard): Find the City of "BLK-PUN-02" with an exact match.
- Scheme: fix #REF! (Hard): =VLOOKUP("PMK-002",Scheme!$A$2:$C$4,4,FALSE) gives #REF!. Fix it to return the District.
- Scheme: safe amount (Hard): Find the Amount of Scheme!F2 with TRIM. Show 0 if there is any error.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
