Lookup · find a position · Nov 2026

The MATCH function,
measured & explained.

Syntax, a live playground, a measured match_type gallery (0/1/-1), the sorting and #N/A traps, and a sourced error library: finding a value's position.

D1
fx
=MATCH("Carol", A2:A6, 0)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How MATCH works

Returns the 1-based position of lookup_value in lookup_array. match_type 0 is exact and case-insensitive; 1 and -1 are approximate and require sorted data.

=MATCH(lookup_value, lookup_array, [match_type])
lookup_valueThe value to locate.
lookup_arrayThe single row or column to search.
match_type0 exact (default-safe), 1 ascending, -1 descending.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Find a position

What MATCH actually returns

MATCH returns the 1-based position of a value in a range — not the value itself. The match_type argument (0 exact, 1 ascending, -1 descending) is where the surprises live. Every row was measured live in HyperFormula on the sample table. Click any row to load it.

match_type gallery — 0 exact, 1 ascending, -1 descending

ExpressionResultWhy
=MATCH("Carol",A2:A6,0)3Exact match: Carol is the 3rd name
=MATCH("Zoe",A2:A6,0)#N/ANo exact match raises #N/A
=MATCH("carol",A2:A6,0)3Exact match is case-insensitive
=MATCH(75000,C2:C6,0)3Numeric exact match on the salary column
=MATCH(75,E2:E6,1)3Type 1: largest value ≤ 75 is 70 at position 3
=MATCH(95,E2:E6,1)5Type 1: 95 caps at the top value 90 (position 5)
=MATCH(40,E2:E6,1)#N/AType 1: below the smallest value raises #N/A
=MATCH(85,F2:F6,-1)2Type -1: smallest value ≥ 85 is 90 at position 2

The position, not the value

ExpressionResultWhy
=MATCH("Carol",A2:A6,0)3MATCH hands back a row number…
=INDEX(C2:C6,MATCH("Carol",A2:A6,0))75000…and INDEX turns that position into the value
⚠ MATCH returns a position, not the value — pair it with INDEX to fetch the cell. match_type 1 needs the range sorted ascending; match_type -1 needs it sorted descending; an unsorted range gives wrong or #N/A results. Exact match (0) is case-insensitive and, in Excel, accepts the * and ? wildcards.
MATCH + INDEX is the flexible lookup: MATCH finds the row, INDEX fetches any column — including columns to the left of the key, which VLOOKUP cannot reach.
// 03 — Try it live

Interactive MATCH playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see MATCH find an exact position, walk ascending and descending ranges, and feed INDEX for a full lookup.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

// 05 — Cross-platform

Where MATCH works

MATCH is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2003.

PlatformSupportNotesGotcha
✓ Supported full MATCH◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master position lookups

MATCH is one piece. The full course walks the toolkit end to end with live, measured examples.

  • MATCH vs VLOOKUP — position vs value
  • The three match_type modes and their sort rules
  • The #N/A traps and IFNA guards
  • MATCH + INDEX for left-lookups and insert-safe formulas
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked