How to Use the MATCH Function in Google Sheets
=MATCH(search_key, range, [search_type]). Use 0 for an exact match.
What MATCH does
MATCH returns a position, not the value itself. Position 3 means "third item in the list". On its own that is mildly useful. Paired with INDEX it becomes a flexible lookup, as in the INDEX and MATCH guide.
MATCH syntax
=MATCH(search_key, range, [search_type])
search_key: the value to search for.range: a one-dimensional range, a single row or column. Google's page says a range with both height and width above 1 returns #N/A.search_type(optional, default 1): how to match.
| search_type | Meaning |
|---|---|
1 (default) | The range is sorted ascending. Returns the position of the largest value less than or equal to the key. |
0 | Exact match. Use this when the range is not sorted. |
-1 | The range is sorted descending. Returns the position of the smallest value greater than or equal to the key. |
A step-by-step example: exact match
Here are weekdays in column A and sorted numbers in column B, in cells A1:B6.
| A | B | |
|---|---|---|
| 1 | Day | Limit |
| 2 | Monday | 10 |
| 3 | Tuesday | 20 |
| 4 | Wednesday | 30 |
| 5 | Thursday | 40 |
| 6 | Friday | 60 |
=MATCH("Wednesday", A2:A6, 0)
This returns 3, because Wednesday is the third item in the list.
Approximate match
Leave out the last argument and MATCH assumes an ascending list and finds the largest value that does not go over your key:
=MATCH(50, B2:B6)
This returns 4. The values 10, 20, 30 and 40 are all at most 50, and 40 is the largest, in position 4. The next value, 60, is too big.
Common mistakes
- Forgetting the 0. Without it MATCH assumes a sorted list and can return a wrong position.
- Using a two-dimensional range. It must be one row or one column.
- Expecting the value back. MATCH returns a position. Use INDEX to get the value.
Try it yourself
Use the table above (A1:B6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Exact position
Write a formula that returns the position of Wednesday in A2:A6, using an exact match.
Exercise 2: Approximate position
Write a formula that returns the position of the largest value that does not exceed 50 in the sorted list B2:B6.
What to learn next
- INDEX and MATCH: look up a value in any direction.
- VLOOKUP: fetch one value from a table.
- XLOOKUP: look up a value in any direction.
Syntax and rules in this guide were checked against Google's MATCH help page. See all guides.