VLOOKUP, IFERROR and inserted columns: formula traps to check in Excel™
Some formulas work today and fail quietly later. Three habits cause most of it.
Trap 1: the column number
=VLOOKUP(A2, Products!$A$2:$D$6, 2, FALSE) returns the second column of the range. Insert a column inside that range and "2" may now point at something else. The formula still returns a value, so nothing shouts. (The ranges here are illustrative.)
Safer: INDEX with MATCH, or XLOOKUP in Microsoft 365, which refer to a return column directly, not by position.
Trap 2: IFERROR hiding a real problem
=IFERROR(VLOOKUP(...), 0) turns every error into zero. A failed lookup, a mistyped code and a deleted row all show as zero, and a sum quietly drops them.
Safer: catch only the case you expect (IFNA), and show a visible marker such as "not found", not a number.
Trap 3: approximate match
VLOOKUP with TRUE, or with the last argument left out, expects sorted data and returns the nearest match instead of failing. On unsorted data, the result may be wrong with no error.
Check an inherited sheet
These steps apply to Excel™ in Microsoft 365.
- Press Ctrl+` to show formulas.
- Use Ctrl+F with Look in set to Formulas to search for
VLOOKUP,IFERRORandTRUE). - For each hit, check the column number against the range, the fallback value and the match type.
- Test the lookup with a code you know is missing and see what it shows.
Flag them as you click
Selection-Scoped Formula Explainer has a Watch out panel that flags IFERROR swallowing real errors, VLOOKUP approximate match, formulas that may break when a column is inserted, whole-column references, volatile functions and cross-workbook links. It explains; it does not rewrite your formulas. Explaining any cell is free.
See Selection-Scoped Formula Explainer for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Selection-Scoped Formula Explainer is not affiliated with or endorsed by Microsoft.