How to understand iferror(vlookup(...)) chains in inherited excel models
- Step 1Find the lookup chain in the inherited sheet — Click the cell holding the IFERROR/VLOOKUP chain and copy its text from the formula bar. A representative cell is enough — the chain is identical down the fill range.
- Step 2Confirm Pro access — The Explainer is Pro and above. Free returns
Formula Explainer requires Pro tier.and nothing else. There's no size cap on the pasted formula. - Step 3Paste the chain into the single field — One input, the
formulafield — no upload, no options. The leading=is optional. The breakdown appears instantly. - Step 4Read the fallback ladder top-down — The outer IFERROR is first; its
fallback if error:argument is the next lookup, whose own fallback is the next, and so on. Each level is indented two spaces, so the ladder is visible. - Step 5Verify each VLOOKUP's column index and range — Confirm the
table range:andcolumn index:of each lookup. A common inherited-model bug is two stacked VLOOKUPs that pull different column indexes from tables that have since been re-laid-out — the labelled breakdown makes that mismatch obvious. - Step 6Pair with data-flow tools for the full picture — The Explainer shows logic. To verify the referenced tables exist and aren't broken, use the error locator; to trace the precedent cells, use the dependency map; to find references to other files, use the external link auditor.
VLOOKUP and IFERROR arguments, as labelled
Both functions are on the labelled list, so every argument in a typical chain gets a name. Note VLOOKUP's fourth argument is always called exact-match flag regardless of whether you wrote FALSE, TRUE, 0, or 1.
| Function : argument | Meaning | Typical inherited-model value |
|---|---|---|
VLOOKUP : lookup value | The key being searched for | A2 (the row's id) |
VLOOKUP : table range | The table to search, first column = key | Rates!$A$2:$D$500 (absolute, cross-sheet) |
VLOOKUP : column index | Which column number to return | 4 (often the rate or price column) |
VLOOKUP : exact-match flag | FALSE/0 = exact, TRUE/1 = approximate | 0 or FALSE — exact match |
IFERROR : expression | The formula to try first | the primary VLOOKUP |
IFERROR : fallback if error | What to use if it errors | a second IFERROR(VLOOKUP) or "" |
What the report actually contains
The Explainer outputs a single plain-text report (outputType report). There is no file download, no chart, no colour highlighting — it is text you read or copy.
| Part of the report | What it shows | Notes |
|---|---|---|
Formula: line | The cleaned formula, echoed back with a leading = added if you omitted it | Confirms exactly what was parsed |
A 60-character ─ rule | A visual separator | Pure formatting |
Step-by-step breakdown: | The heading for the tree | Always present |
| The indented tree | One line per node, indented two spaces per nesting level, outermost function first | This is the substance of the output |
Where the Formula Explainer fits in the tier and limit model
The Explainer reads a pasted formula string, not a file, so the byte/row/file ceilings that gate the file-based Excel tools are not the binding constraint here — the binding constraint is the Pro-tier gate. Numbers shown are the documented Excel-family limits for the file tools, for context.
| Tier | Formula Explainer access | Excel file-tool ceiling (context) |
|---|---|---|
| Free | No access — returns the error Formula Explainer requires Pro tier. | 5 MB / 10,000 rows / 1 file |
| Pro | Full access — paste any formula, unlimited explanations | 50 MB / 100,000 rows / 5 files |
| Pro-media | Full access | 200 MB / 500,000 rows / 20 files |
| Developer | Full access | 500 MB / unlimited rows / unlimited files |
| Enterprise | Full access | 2 GB / unlimited rows / unlimited files |
Cookbook
Real inherited-model lookup chains with the exact breakdown the tool produces. Table and sheet names are illustrative.
IFERROR wrapping a single VLOOKUP
The baseline pattern: try the lookup, blank on error. The fourth VLOOKUP argument is labelled exact-match flag whether the author wrote FALSE or 0.
Paste:
=IFERROR(VLOOKUP(A2,Rates!$A$2:$D$500,4,FALSE),"")
Breakdown:
IFERROR() — Returns a value if a formula produces an error; otherwise returns the formula result
expression: VLOOKUP() — Searches for a value in the first column of a range and returns a value from another column
lookup value: cell reference A2
table range: cell range Rates!$A$2:$D$500
column index: number 4
exact-match flag: boolean FALSE
fallback if error: text ""A two-table fallback ladder
Primary table, then a backup table, then blank. The breakdown shows the nested IFERROR clearly: the outer fallback IS the inner IFERROR(VLOOKUP). This is where parentheses-counting fails and the tree helps most.
Paste:
=IFERROR(VLOOKUP(A2,Rates!$A$2:$D$500,4,0),IFERROR(VLOOKUP(A2,Backup!$A$2:$D$500,4,0),""))
Breakdown:
IFERROR() — Returns a value if a formula produces an error; otherwise returns the formula result
expression: VLOOKUP() — Searches for a value in the first column of a range and returns a value from another column
lookup value: cell reference A2
table range: cell range Rates!$A$2:$D$500
column index: number 4
exact-match flag: number 0
fallback if error: IFERROR() — Returns a value if a formula produces an error; otherwise returns the formula result
expression: VLOOKUP() — Searches for a value in the first column of a range and returns a value from another column
lookup value: cell reference A2
table range: cell range Backup!$A$2:$D$500
column index: number 4
exact-match flag: number 0
fallback if error: text ""IFERROR wrapping INDEX/MATCH instead of VLOOKUP
Many inherited models migrated from VLOOKUP to INDEX/MATCH for left-lookups but kept the IFERROR wrapper. Same ladder shape, INDEX/MATCH inside.
Paste:
=IFERROR(INDEX(Rates!$D:$D,MATCH(A2,Rates!$A:$A,0)),"")
Breakdown:
IFERROR() — Returns a value if a formula produces an error; otherwise returns the formula result
expression: INDEX() — Returns the value at a given position in a range
range: Rates!$D:$D
row number: MATCH() — Returns the position of a value within a range
lookup value: cell reference A2
lookup range: Rates!$A:$A
match type: number 0
fallback if error: text ""
(Sheet-qualified whole-column ranges such as Rates!$D:$D appear as
raw text, not 'cell range'; a row-bounded range like
Rates!$D$2:$D$500 shows the 'cell range' label.)Spotting a mismatched column index
A classic inherited bug: two stacked VLOOKUPs pull different column indexes after a table was re-laid-out. The labelled breakdown makes the 4 vs 3 mismatch jump out — something easy to miss in raw text.
Paste:
=IFERROR(VLOOKUP(A2,T1!$A:$D,4,0),VLOOKUP(A2,T2!$A:$D,3,0))
Breakdown (abridged):
IFERROR()
expression: VLOOKUP()
...
column index: number 4 <-- primary pulls column 4
exact-match flag: number 0
fallback if error: VLOOKUP()
...
column index: number 3 <-- fallback pulls column 3 (mismatch!)
exact-match flag: number 0A quoted sheet name that swallows the rest of the chain
If the rate table lives on a tab named with a space ('Rate Card'), the apostrophe stops the tokeniser mid-argument. IFERROR and VLOOKUP still parse, but everything from the apostrophe onward — the rest of the range, the remaining VLOOKUP arguments, AND the IFERROR fallback — gets absorbed into the table range: node as raw text. The fallback is silently lost from the tree. Rename the tab or strip the qualifier to read the real chain.
Paste:
=IFERROR(VLOOKUP(A2,'Rate Card'!$A:$D,4,0),"")
Breakdown:
IFERROR() — Returns a value if a formula produces an error; otherwise returns the formula result
expression: VLOOKUP() — Searches for a value in the first column of a range and returns a value from another column
lookup value: cell reference A2
table range: 'Rate Card'!$A:$D,4,0),"")
(From the apostrophe on, the rest is one raw chunk — the column
index, match flag, and the IFERROR fallback are gone. Rename the
tab to 'RateCard' so 'RateCard!$A:$D' parses and the full chain shows.)Edge cases and what actually happens
Free tier blocked entirely
Pro requiredThe Explainer is gated to Pro and above. On Free it returns Formula Explainer requires Pro tier. with no partial output. There is no smaller free preview — the whole tool is behind the gate. Upgrade to Pro (or higher) to use it.
VLOOKUP fourth argument written as 0 vs FALSE
Both labelledWhether the author wrote FALSE, TRUE, 0, or 1 for VLOOKUP's match argument, the breakdown labels it exact-match flag. FALSE/TRUE render as boolean FALSE/boolean TRUE; 0/1 render as number 0/number 1. The label is the same — only the value node differs.
Quoted sheet name with a space
Not parsedA reference like 'Sales 2026'!A1 uses an apostrophe, which the tokeniser does not recognise. From that apostrophe onward the rest of the formula is absorbed as one raw chunk: if the quoted ref is at the very start, the whole formula dumps as a single raw line; if it sits inside an argument, the surrounding functions parse but that argument swallows everything after the apostrophe — so any later arguments are dropped from the tree. Workaround: rename the sheet so it has no spaces (then Sales2026!A1 parses), or strip the '…'! qualifier before pasting if you only need the function structure.
Function name not in the description list
By designAny function that is not one of the 65 named ones — a valid-but-unlisted Excel function like SUMPRODUCT, OFFSET, or INDIRECT, a Google Sheets-only function like QUERY or ARRAYFORMULA, or a custom workbook UDF like MyMacro — renders as NAME() — Excel function (uppercased) with generic arg N labels. It is never mislabelled User-defined function and never throws; you still see the full nesting structure and every argument, just without a prose description of that one function.
Expecting a calculated result
By designThe Explainer is static analysis: it never evaluates the formula and never reads cell values, so it cannot tell you what =VLOOKUP(...) returns for your data — only what each part does and how the parts nest. To see a value, run the formula in Excel; to understand it, use this. For tracing which cells feed a result with real values, use the dependency map.
Structured table reference
Column droppedExcel table syntax is not fully tokenised. Table1[Amount] keeps Table1 as a plain cell reference but drops the [Amount] column part. A reference that starts with a bracket, like [@Price], starts with an unrecognised character, so it and the rest of the formula dump as one raw line. Replace structured references with the equivalent A1 ranges before pasting if you need them in the tree.
Same formula, different explanation?
Cannot happenThe breakdown comes from a deterministic recursive-descent parser, not a language model, so an identical formula always produces an identical tree. There is no temperature, no sampling, and no chance of an AI confidently inventing a function or mis-describing an argument — the descriptions are fixed text keyed to each function name.
Unbalanced or truncated formula
Partial parseA formula missing its closing parenthesis, e.g. =IF(A1>0, does not crash the tool — it parses and explains the part it understood and stops. Use this to your advantage: paste a half-finished formula to sanity-check the structure so far, then complete it in Excel.
Empty or whitespace-only input
No formula providedIf the formula box is empty or only whitespace, the tool returns No formula provided. rather than an empty tree. Paste the real formula text. A leading = is optional — it is added automatically if you leave it off.
Frequently asked questions
How does it help with a stacked IFERROR(VLOOKUP, IFERROR(VLOOKUP)) chain?
It prints the chain as an indented ladder: the outer IFERROR first, its fallback if error: being the next IFERROR(VLOOKUP), and so on inward. You see the primary table, each fallback table, and the final blank at a glance — instead of counting parentheses and risking a misread.
Can I confirm which column each VLOOKUP returns?
Yes. VLOOKUP is fully labelled, so each lookup shows column index: number N. Comparing the indexes across a fallback ladder is the fastest way to catch the classic inherited bug where two stacked lookups pull different columns after a table was re-laid-out.
Why does the fourth VLOOKUP argument always say 'exact-match flag'?
That's its label regardless of the value. If the author wrote FALSE it shows exact-match flag: boolean FALSE; if they wrote 0 it shows exact-match flag: number 0. Both mean exact match; the label is constant and only the value node changes.
Will it parse cross-sheet ranges like Rates!$A$2:$D$500?
Yes — unquoted sheet names with absolute ranges tokenise cleanly and show as cell range Rates!$A$2:$D$500. You can see exactly which table each lookup in the ladder consults. The exception is sheet names with spaces, which use apostrophes and don't parse.
My rate table is on a tab named with a space — why does it break?
A reference like 'Rate Card'!$A:$D uses an apostrophe the tokeniser doesn't recognise, so the formula dumps onto one raw line. Rename the tab to remove the space (e.g. RateCard), or strip the '…'! qualifier before pasting if you just need the chain structure.
Is the breakdown safe to put in audit working papers?
Yes — it's reproducible. A deterministic parser, not an AI, generates it, so re-running the same chain always yields the same tree. Paste the report text into your note; a reviewer who re-runs it gets identical evidence.
Does it tell me whether the primary or fallback lookup actually fired?
No — it never evaluates, so it can't say which branch your data triggers. It shows the structure of every branch. To see which fired, run the formula in Excel; to trace the cells involved, use the dependency map.
What about VLOOKUP returning #N/A — will it still explain?
Yes — a chain that would return #N/A at runtime has perfectly normal text, so it parses and explains in full (the tool never runs it). The only caveat is a literal error token sitting in the text itself: a #REF! left behind by a deleted range starts with #, which the parser doesn't recognise, so it swallows the rest of that argument. Replace it with a placeholder cell before pasting if you need the whole chain.
Is the inherited workbook's logic uploaded?
No. The parser runs entirely in your browser — the formula text never leaves your device, nothing is logged, and nothing is stored. This matters when the formula encodes confidential business logic (pricing rules, model assumptions, internal thresholds). The only server interaction is the tier check that confirms you are on Pro or above.
Why Pro tier?
The Formula Explainer is gated to Pro and above. On the Free tier the tool returns Formula Explainer requires Pro tier. and does nothing else. The gate is on the tool, not on formula size — there is no per-formula byte or character limit, because the input is a short string rather than a file. Pro, Pro-media, Developer, and Enterprise all have full, unlimited-explanation access.
Does the workbook's size matter?
No. Unlike the file-based Excel tools — which cap at 5 MB / 10,000 rows on Free up to 500 MB / unlimited on Developer — this tool takes a pasted formula string and never reads a workbook. There is nothing to upload, so the byte and row ceilings do not apply. If you want to explain formulas that live inside a workbook, open the file in Excel, copy the formula text from the formula bar, and paste it here.
What if I paste nothing?
The tool returns No formula provided. — it does not guess or produce a blank tree. Paste the actual formula text, with or without the leading =; a missing = is added automatically before parsing.
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.