MATCH Function in Google Sheets
Returns the position of a value in a one-dimensional range. Usually paired with INDEX to perform lookups, or used standalone to find where a value lives in a list.
Syntax
MATCH(search_key, range, [search_type])Returns: The 1-based position of the match in range, or #N/A if not found.
Excel equivalent: MATCH (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| search_key | Required | The value to search for. |
| range | Required | A single row or column to search in. |
| search_type | Optional | 1 (default) = largest value ≤ search_key, range sorted ascending. 0 = exact match (most common). -1 = smallest value ≥ search_key, range sorted descending. |
Examples
Find a name in a column
=MATCH("Alice", A:A, 0)Returns the row number where "Alice" first appears in column A. The 0 forces exact match.
Combine with INDEX for a left-lookup
=INDEX(A:A, MATCH("SKU-123", C:C, 0))Find "SKU-123" in column C, then return the value at the same row in column A.
Check if a value exists in a list
=ISNUMBER(MATCH(A2, ApprovedList!A:A, 0))MATCH returns a number on hit, #N/A on miss. ISNUMBER converts that to TRUE/FALSE for use in conditional checks.
When to use an alternative
- XMATCH — You want the modern version with built-in wildcard and reverse-search options.
- FIND — You want to find a substring inside a single string, not search a range.
- COUNTIF — You only want to know IF a value exists, not where.
Common errors and how to fix them
#N/A
Cause: search_key not in range, or search_type defaulted to 1 on an unsorted column.
Fix: Add 0 (or FALSE) as the third argument for exact match.
Wrong position returned
Cause: Duplicate matches in range — MATCH returns the FIRST one only.
Fix: Use XMATCH with search_mode -1 for the last match, or wrap in a FILTER for all matches.
Related functions
Frequently Asked Questions
What does MATCH return when the search type is wrong?
If you leave search_type as the default 1 (ascending search) and your range isn't sorted, MATCH returns the position of the largest value less than or equal to search_key — which is almost never what you wanted. Always pass 0 explicitly for exact match.
Can MATCH find multiple positions?
No, MATCH returns the first hit only. For multiple matches use FILTER, or wrap multiple MATCH calls with different starting points using OFFSET.