How to Use MATCH in Google Sheets: Comprehensive Guide

MATCH returns the position of a value in a single row or column, not the value itself. On its own it answers "where is this item in the list?" and "is it in the list at all?". Paired with INDEX, it forms the most flexible lookup in Google Sheets.

Syntax

=MATCH(search_key, range, [search_type])

search_key: The value to find.
range: A single row or single column to search. A multi-column range returns #N/A.
search_type [Optional]:
- 0 exact match, no sorting needed. This is almost always what you want.
- 1 (default) finds the largest value less than or equal to the key. The range must be sorted ascending.
- -1 finds the smallest value greater than or equal to the key. The range must be sorted descending.

Note that the default is 1, an approximate match. If you leave out the third argument and the data is not sorted, you can get a wrong position with no error. Write 0 explicitly for exact lookups.

Basic examples

With fruit names in A1:A5 (apple, banana, cherry, date, elderberry):

=MATCH("cherry", A1:A5, 0) returns 3.
=MATCH("fig", A1:A5, 0) returns #N/A, because there is no exact match.
=MATCH("ch*", A1:A5, 0) returns 3. With 0, wildcards work (* for any text, ? for one character).

The result is the position within the range. If the range is A3:A7, a match in the first cell returns 1, not 3.

Approximate match for bands

With a sorted list of thresholds in A1:A5 (0, 60, 70, 80, 90):

=MATCH(84, A1:A5, 1)

Returns 4, the position of 80, the largest threshold that does not exceed 84. Used with INDEX, this turns a score into a grade without nested IF formulas:

=INDEX({"F", "D", "C", "B", "A"}, MATCH(84, A1:A5, 1))

Returns B.

MATCH with INDEX

MATCH finds the row, INDEX returns the value from it:

=INDEX(C2:C100, MATCH("Ben", A2:A100, 0))

This returns the item in column C on the same row where Ben appears in column A. It works with the key on either side of the result, and is the foundation of the two-way lookup:

=INDEX(B2:M20, MATCH("North", A2:A20, 0), MATCH("Mar", B1:M1, 0))

See INDEX for more.

Practical patterns

Check whether a value exists

=ISNUMBER(MATCH(E2, A2:A100, 0))

Returns TRUE if the value is in the list and FALSE if not. MATCH returns a number when found and #N/A when not, and ISNUMBER converts that to a clean true/false. It is handy in data validation and conditional formatting.

Find a column by its header

=MATCH("Revenue", A1:Z1, 0)

Returns the column position of the Revenue header. Feed it to INDEX so a formula keeps working even if someone reorders the columns.

Find the position of the maximum

=MATCH(MAX(B2:B100), B2:B100, 0)

Returns where the highest value is, which INDEX can use to get the label next to it:

=INDEX(A2:A100, MATCH(MAX(B2:B100), B2:B100, 0))

First non-blank or first match from an array

MATCH(TRUE, ARRAYFORMULA(condition), 0) returns the position of the first item for which a condition is true, for example the first value above 100:

=MATCH(TRUE, ARRAYFORMULA(B2:B100 > 100), 0)

Common errors

#N/A. No match. For exact lookups, check for trailing spaces (use TRIM) and for text versus numbers. Also confirm that you used 0.
Wrong position, no error. You omitted search_type (so it defaulted to 1) on unsorted data. Add 0.
#N/A with a multi-column range. MATCH only searches a single row or column.

Frequently asked questions

Is MATCH case-sensitive? No. "Apple" and "apple" match. Use EXACT inside an array formula for case-sensitive matching.

What if the value appears more than once? MATCH returns the position of the first occurrence.

What is the difference between MATCH and VLOOKUP? VLOOKUP returns a value from the matching row. MATCH returns only the position, which makes it more flexible and reusable.

Related Functions

INDEX: Returns the value at a position, the usual partner of MATCH.
VLOOKUP: Vertical lookup in a range.
HLOOKUP: Horizontal lookup in a range.
XLOOKUP: A modern lookup that replaces both.

MATCH