Skip to content
Guide: what silently breaks models

The 5 silent spreadsheet errors that cost you money

Most spreadsheet mistakes do not throw errors. They return plausible numbers. SheetSage scans Google Sheets to flag the highest-risk formula patterns and helps you fix them with snapshot rollback.

Install Free Audit checklist See the 5 rules

No new account: runs inside Google Sheets.

Works inside Google Sheets Snapshot rollback No spreadsheet data sent externally by default
SheetSage
Ranked issues + safer fixes
Health score
Top risk cluster
R1: VLOOKUP match mode
Missing/unsafe 4th argument
Safer workflow
Fix → snapshot
One-click restore
Run scan

Get a rule-by-rule report (R1–R5).

Install
Cluster repeats Restore anytime
On this page

This page is a practical checklist of the silent spreadsheet failure modes that show up in finance models, ops dashboards, pricing sheets, and any workbook that must be right. Each section includes a safer pattern and how SheetSage detects it today.

Safe formulas (copy/paste)

These are safe defaults when you need exact matches and fail-loud behavior:

=IFNA(VLOOKUP(A2, PriceTable, 2, FALSE), "NOT FOUND")
=IFNA(MATCH(A2, A:A, 0), "NOT FOUND")

Note: approximate match can be correct for tier bands, but it must be explicit and the table must be sorted.

The 5 core rules SheetSage detects today

SheetSage uses a rule-based scanner to flag common silent-failure patterns, then groups repeated formulas into clusters so you can fix the pattern once and apply it everywhere (with snapshot rollback).

R1: VLOOKUP match mode
Missing/unsafe 4th argument can return wrong rows.
R2: VLOOKUP column index
Hardcoded indexes break after structure changes.
R3: MATCH exactness
Missing ,0 can shift positions silently.
R4: Reference drift
Unanchored ranges drift when copied across rows.
R5: Magic numbers
Hardcoded constants hide assumptions and break audits.

R1 — The VLOOKUP match-mode trap

VLOOKUP can "work" while returning the wrong row when match mode is missing, unsafe, or dynamic. When the 4th argument is omitted, VLOOKUP behaves like approximate match — which is only correct for specific use cases (like tier bands) and requires sorted data.

Citation block

Definition: a VLOOKUP with missing/unsafe match mode can return a plausible value from the wrong row.

Safer default (exact):

=IFNA(VLOOKUP(A2, PriceTable, 2, FALSE), "NOT FOUND")

If approximate match is intentional (tier bands), make it explicit and keep the table sorted.

Full breakdown: VLOOKUP approximate match trap. Related: MATCH exactness.

R2 — Column index integrity (the inserted-column disaster)

Hardcoded column numbers silently shift meaning after structure changes. Your formula still returns a value — just from the wrong field.

Citation block

Definition: invalid/out-of-bounds or brittle VLOOKUP column indexes can return the wrong field after edits.

Best practice: stop counting columns. Match the header, then index the matched column.

Full breakdown: VLOOKUP column index risk. Related: reference drift.

R3 — MATCH exactness trap (missing ,0)

MATCH is often used inside INDEX/MATCH. If MATCH returns the wrong position, everything downstream becomes wrong — quietly. The safest default is exact match: ,0.

Citation block

Safer default:

=IFNA(MATCH(A2, A:A, 0), "NOT FOUND")

Full breakdown: MATCH exact vs approximate. Related: VLOOKUP match mode.

R4 — Reference drift (anchoring)

If your lookup ranges are not anchored, copy/paste becomes a slow-motion failure mode. Values still calculate — but ranges "walk" away from intended data.

Citation block

Definition: ranges that shift when copied cause lookups to reference the wrong data over time.

Safer pattern: anchor stable ranges like $B$5:$C$100.

Full breakdown: absolute references and drift. Related: VLOOKUP column index risk.

R5 — Magic numbers (hardcoded assumptions)

Magic numbers hide business assumptions inside formulas. That makes scenarios hard to run ("did we update all instances?") and audits painful.

Citation block

Definition: hardcoded constants in formulas hide assumptions and reduce auditability.

Best practice: move assumptions into labeled cells (or named ranges) and reference them.

Full breakdown: magic numbers in spreadsheets. Related: reference drift.

Bonus: 2 more silent killers (watchlist)

These patterns are common spreadsheet failure modes. They are not part of the current core auto-detection set (R1–R5), but you should still watch for them:

  • Circular references: results can vary by iterative calculation settings.
  • Copy-paste cascades: month 1 is correct, copied 11 times, subtle shifts compound.

What you get when you run a SheetSage scan

  • A rule-by-rule breakdown (R1–R5) with counts and severity.
  • Grouped clusters of similar formulas (fix the pattern once).
  • Snapshot + restore rollback for changes you apply.
Run it on your sheet

Find silent errors before they become decisions

Install SheetSage from the Google Workspace Marketplace, run a scan, then decide what to fix (with rollback).

Install Free See pricing

Always review changes. Snapshot rollback included.

FAQ

Why do spreadsheets return the wrong value with no error?

Many formula patterns fail politely: the output is plausible even when it is wrong (approximate matching, drifted ranges, or shifted column meanings).

Do I always need FALSE in VLOOKUP?

If you want exact matching, yes: be explicit. If approximate matching is intentional (tier bands), keep it explicit and keep the table sorted.

Does SheetSage send my spreadsheet data anywhere?

Spreadsheet content is designed to stay inside Google Sheets. If you upgrade, subscription checks send only an anonymous client ID (no formulas, values, sheet names, or cell addresses).

What does SheetSage fix automatically?

Some fixes are safe and automated (like appending ,0 for missing MATCH exactness). Others require review or confirmation when intent matters (like enforcing VLOOKUP exact match).