MATCH Function in Google Sheets
Use MATCH to find the position of a value in a row or column — the number you feed to INDEX, or a quick way to check whether something exists in a list.
=MATCH("apple", A2:A, 0)
Returns the row position of "apple" within A2:A. The 0 forces an exact match.
How it works
MATCH takes a search value, a single-column or single-row range, and a match type: 0 for an exact match (the one you almost always want), 1 for the largest value less than or equal to the key (range must be sorted ascending), and -1 for the smallest value greater than or equal (sorted descending). It returns a position number, not the value — pair it with INDEX to fetch the value at that position.
Variations
INDEX + MATCH lookup
=INDEX(C2:C, MATCH("apple", A2:A, 0))
MATCH finds the row, INDEX returns the value from another column — a flexible VLOOKUP replacement.
Check if a value exists
=ISNUMBER(MATCH("apple", A2:A, 0))
MATCH returns #N/A when not found; ISNUMBER turns that into TRUE/FALSE.
Match across a row
=MATCH("Q3", A1:Z1, 0)
The range can be a single row to find a column position.
Approximate match on sorted data
=MATCH(A2, $E$2:$E$10, 1)
Match type 1 finds the largest value <= A2; the range must be sorted ascending.
Examples
| Scenario | Formula |
|---|---|
| Row of a product name | =MATCH("Widget", A2:A, 0) |
| Two-way lookup with INDEX | =INDEX(A2:F, MATCH("Widget", A2:A, 0), MATCH("Price", A1:F1, 0)) |
| Is the ID in the list? | =ISNUMBER(MATCH(A2, D:D, 0)) |
FAQ
Why does MATCH return #N/A?
The value wasn't found. With match type 0 the value must appear exactly; check for stray spaces or wrap in IFERROR/ISNUMBER to handle misses.
What does the 0 in MATCH mean?
It's the match type for an exact match. Use 1 or -1 only for approximate matches on sorted ranges.
How is MATCH different from INDEX?
MATCH returns the position of a value; INDEX returns the value at a position. Together they perform a flexible lookup.