Skip to content

Home Guides MATCH exactness

Rule R3 (MATCH exactness)

MATCH without ,0 is a silent risk (here’s the fix)

MATCH is often used inside INDEX/MATCH. If MATCH returns the wrong position, everything downstream becomes wrong — quietly.

Install Free Back to the 5 rules
On this page

Quick fix (copy/paste)

Use exact matching:

=MATCH(A2, A:A, 0)

Fail loud:

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

What this error is

A MATCH call missing the third argument: MATCH(lookup_value, lookup_array). In Google Sheets, the third argument controls match behavior:

  • 0 = exact match (safe default for identifiers)
  • 1 or omitted = approximate behavior (requires sorted data)
  • -1 = approximate in the other direction (also requires sorted data)

Why it is risky

  • Approximate behavior can return the wrong position without error.
  • Wrong position → wrong INDEX result → wrong final value.
  • Outputs look clean, so errors survive reviews.

How SheetSage detects + fixes this (R3)

SheetSage flags MATCH formulas missing the third argument and can auto-fix those cases by appending , 0 (exact match). This is a safe fix when the intent is a true lookup (IDs, SKUs, employee numbers).

If your sheet intentionally relies on approximate behavior, keep it explicit and document the sorted-data requirement.

Edge cases

If approximate matching is intentional (tier bands, sorted rate tables), make it explicit and document assumptions:

  • Sort the lookup array consistently.
  • Validate results with a small test set (including boundary values).
  • Prefer fail-loud patterns for identifiers.

FAQ

What does the third argument actually change?

It controls exact vs approximate behavior. For most lookups (IDs/SKUs), the safe default is 0.

Why does INDEX/MATCH sometimes return the wrong row?

Because MATCH returned the wrong position (often due to approximate behavior). Then INDEX retrieves the wrong row/column.

Do I need sorting?

Only for approximate behavior. If you use 0 (exact), sorting is not required.