How to find every #n/a error from vlookup and index/match with a cell-by-cell report
- Step 1Save after your last data change — Lookups break when source data changes, so save the workbook in Excel after updating tables. The locator reads the saved state's cached results.
- Step 2Open the Error Locator — Go to the Error Locator. No options to set — it always scans every cell on every sheet for all error types,
#N/Aincluded. - Step 3Upload the workbook — Drop the
.xlsx. One file per run; SheetJS parses it locally. The lookup tables behind your formulas stay on your machine. - Step 4Scan the count and red addresses — The panel shows the total error count and the first 10 as
[sheet] cell: errorType. A run of#N/Ain one column signals a single root cause to fix once. - Step 5Download the cell-by-cell fix report — Copy or Download for the full report — each line
[sheet] cell: errorType (=formula). The formula reveals the lookup function and the key column to check. - Step 6Fix the data or formula, then re-scan — Resolve the root cause in Excel — trim keys, fix the column index, switch to exact match, or wrap with
IFNAfor legitimate misses — save, and re-upload to confirm zero#N/A.
Common causes of #N/A in lookups
Why a VLOOKUP/INDEX-MATCH returns #N/A and what to change. The locator's report shows you the formula; this table tells you what to look for.
| Cause | Symptom in data | Fix in Excel | Related error often seen |
|---|---|---|---|
| Key has trailing/leading space | Lookup value looks identical but won't match | TRIM the key column or the lookup value | — |
| Number stored as text | '001 won't match numeric 1 | Coerce both sides to the same type | #VALUE! nearby |
| Approximate match on unsorted data | Wrong/no match returned | Use exact match (FALSE / 0) | — |
| Source code/value changed | Key no longer exists in the table | Update the table or the lookup key | — |
| Column index past table edge | Index exceeds table width | Reduce the column index | #REF! if range deleted |
| Lookup range shrank | Table no longer covers the key | Extend the range / use a Table | — |
Reading a #N/A fix-report line
The formula is the diagnostic. Spotting the function and arguments tells you which side of the lookup to fix.
| Part | Example | What to do with it |
|---|---|---|
| Sheet | [Orders] | Open this tab in Excel |
| Cell | F12 | Go straight to the broken lookup |
| Error type | #N/A | Confirms it's a lookup miss, not a #REF! |
| Formula | (=VLOOKUP(B12,Prices,2,0)) | Names the lookup (Prices) and key (B12) to check |
Tier limits
Free-tier tool. Limits apply to the uploaded file (Excel family), independent of how many #N/A cells exist.
| Tier | Max file size | Max rows | Files per run |
|---|---|---|---|
| Free | 5 MB | 10,000 | 1 |
| Pro | 50 MB | 100,000 | 5 |
| Pro + Media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
VLOOKUP/INDEX-MATCH fix reports in the tool's exact output. The formula on each line is the clue to the root cause — fix the data once and a whole column clears.
A column of VLOOKUP #N/A from a changed code
Product codes were re-keyed in the source, so every lookup against the old codes returns #N/A. One report shows the whole affected column.
Report: Found 3 error cell(s) across 1 sheet(s): [Orders] F12: #N/A (=VLOOKUP(B12,Prices,2,0)) [Orders] F13: #N/A (=VLOOKUP(B13,Prices,2,0)) [Orders] F14: #N/A (=VLOOKUP(B14,Prices,2,0))
INDEX/MATCH #N/A from a trailing space
The MATCH can't find the key because the lookup value has a trailing space. The formula points you to the MATCH argument to TRIM.
Report: Found 1 error cell(s) across 1 sheet(s): [Report] D7: #N/A (=INDEX(Rates,MATCH(A7,Codes,0)))
#N/A alongside #VALUE! — same text/number cause
Numbers stored as text both block the match (#N/A) and break arithmetic (#VALUE!). Catching them together saves a second pass.
Report: Found 2 error cell(s) across 1 sheet(s): [Data] E3: #N/A (=VLOOKUP(A3,Tbl,3,0)) [Data] F3: #VALUE! (=E3*1.2)
#N/A across a lookup on a hidden table sheet
The lookup table lives on a hidden helper sheet; the broken formula is on the visible report tab and is still found.
Report: Found 1 error cell(s) across 1 sheet(s): [Dashboard] C5: #N/A (=VLOOKUP(B5,'_Lookup'!A:B,2,0))
After IFNA / data fix, confirm it's clean
Once you've trimmed keys, fixed types, or wrapped legitimate misses in IFNA, re-scan to confirm no #N/A remains.
Report (after fix + save): No formula errors found in this workbook.
Edge cases and what actually happens
Formula already wraps the lookup in IFNA/IFERROR
By designIf IFNA/IFERROR caught the miss and the cached result is a fallback value, the cell stores no error and isn't flagged. The report shows only un-handled #N/A, which is exactly what you still need to fix.
Trailing-space key looks identical in Excel
DetectedA lookup value with a trailing space won't match and returns #N/A. The locator flags it; the fix is to TRIM the key. For whitespace cleanup across a sheet, see /tool/csv-whitespace-trimmer for CSV or trim in Excel directly.
Number stored as text breaks the match
Detected#N/A often comes from '001 (text) not matching numeric 1. The locator reports the #N/A; to find every numbers-as-text cell behind it, run the format inspector.
More than 10 #N/A errors
By designA broken lookup column usually produces many #N/A. The on-screen preview shows 10, but the count is accurate and the downloadable report lists every one with its formula — the cell-by-cell fix list.
Cell contains literal '#N/A' typed as text
Not detectedOnly cells with a true error type are reported. If #N/A was pasted as a text value, it's text, not an error, so it's skipped.
Lookup against an external workbook
Detected (as cached)If the lookup references another workbook that's unavailable, the cached result may be #N/A or #REF!. The locator reports whatever is stored; use /excel-tools/excel-external-link-auditor to inventory the external links.
File over the free 5 MB / 10,000-row limit
Blocked on freeLarge transaction workbooks can exceed free limits. Upgrade to Pro (50 MB / 100,000 rows) or higher to scan the full file.
Calculation set to manual after a data update
May miss errorsSheetJS reads cached results. If you updated the source table but didn't recalculate, a lookup may still show a stale match. Press F9, save, and re-scan to catch the new #N/A.
Lookup table on a hidden sheet
DetectedThe scan covers hidden sheets, so a #N/A on a visible tab driven by a hidden lookup table is reported, and you can open the hidden table to fix the key.
Frequently asked questions
Does it find #N/A from VLOOKUP and INDEX/MATCH?
Yes. It flags any cell whose stored type is #N/A, regardless of which function produced it — VLOOKUP, HLOOKUP, INDEX/MATCH, or XLOOKUP — and shows the formula so you know which lookup to fix.
Does it tell me why the lookup failed?
It shows the formula and the cell, which point you to the lookup and key to investigate; it doesn't diagnose the data itself. The most common causes are trailing spaces, numbers stored as text, exact-vs-approximate match, and changed source codes.
Will it fix the #N/A automatically?
No — it's a diagnostic fix report. You correct the data or formula in Excel (TRIM keys, fix types, switch to exact match) or wrap legitimate misses in IFNA. Then re-scan to confirm.
Why does my IFNA-wrapped lookup not appear?
Because IFNA returned a fallback value, so the cell stores no error. The report shows only un-handled #N/A, which is what still needs attention.
Can it find #N/A on every sheet?
Yes. The scan walks every sheet, including hidden ones, so a #N/A driven by a lookup table on a helper tab is still reported.
What if my number-as-text is causing the #N/A?
The locator flags the #N/A; to find the underlying numbers-stored-as-text cells, run the format inspector, which lists cells stored as text that look like numbers.
How many #N/A errors can the report show?
All of them in the downloadable report — there's no cap. The on-screen preview is limited to the first 10, but a broken lookup column's full list is always in the report.
Are my lookup tables uploaded anywhere?
No. SheetJS parses the workbook in your browser, so pricing and customer tables behind your lookups stay on your machine.
Why might a #N/A not show up after I updated the source?
If calculation is set to manual and you didn't recalculate, the cached value is stale. Press F9 in Excel, save, then re-scan so the new #N/A is captured.
What file types does it accept?
Excel workbooks. The registry declares .xlsx; the picker also allows .xls and .ods. CSV isn't supported because it has no formulas or stored error types.
Does it find #REF! and #VALUE! too?
Yes — it detects all eight error types in the same scan, which is useful because a broken lookup often produces #REF! (index past the table) or #VALUE! (text/number mismatch) alongside the #N/A.
What else helps me fix lookups?
Use the formula explainer to break down a complex INDEX/MATCH, the format inspector for numbers-as-text, and the dependency map to see which table feeds a lookup.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.