How to Use the MATCH Function in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 6 min read · includes 2 free exercises

In short: MATCH returns the position of a value inside a single row or column. The formula is =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_typeMeaning
1 (default)The range is sorted ascending. Returns the position of the largest value less than or equal to the key.
0Exact match. Use this when the range is not sorted.
-1The 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.

AB
1DayLimit
2Monday10
3Tuesday20
4Wednesday30
5Thursday40
6Friday60
=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

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.

Hint: the last argument is 0.

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.

Hint: leave out the third argument.

What to learn next

Syntax and rules in this guide were checked against Google's MATCH help page. See all guides.