MATCH Function in Google Sheets

MATCH in Google Sheets returns the relative position of a value within a single row or column. It tells you where a value sits in a range, not the value itself. MATCH is most often paired with INDEX to build flexible lookups, but it is also useful on its own when you need a position number.

Parameters

Parameter	Required	Description
search_key	Yes	The value to search for. Can be a number, text string, cell reference, or logical value.
range	Yes	A one-dimensional range (single row or single column) to search within.
search_type	No	Controls the match behavior. 0 = exact match. 1 = finds the largest value less than or equal to search_key (range must be sorted ascending). -1 = finds the smallest value greater than or equal to search_key (range must be sorted descending). Default is 1.

Examples

Find the position of a value

Find the row position of “Gadget” in a product list:

=MATCH("Gadget", A2:A20, 0)

If “Gadget” is in cell A5, this returns 4 (the 4th cell in the range A2:A20). The 0 argument requires an exact match.

Use with INDEX for a two-way lookup

Return the Q2 sales figure for “North” region, where regions are in column A and quarter headers are in row 1:

=INDEX(B2:E10, MATCH("North", A2:A10, 0), MATCH("Q2", B1:E1, 0))

The first MATCH finds the row position of “North.” The second MATCH finds the column position of “Q2.” INDEX returns the value at that intersection.

Approximate match on sorted data

Find the position of the threshold closest to (but not exceeding) a score of 85, where A2:A6 contains sorted values 0, 60, 70, 80, 90:

=MATCH(85, A2:A6, 1)

This returns 4 (the position of 80), because 80 is the largest value that does not exceed 85.

Common Errors

#N/A --- The search key was not found. With search_type set to 0, this means no exact match exists. Check for trailing spaces, case differences (MATCH is not case-sensitive), or data type mismatches between the search key and range values.

Tips

Always use 0 for the third argument when you need an exact match. The default value of 1 assumes your data is sorted in ascending order, and it returns approximate matches. This catches many people off guard.

MATCH returns a position relative to the range, not an absolute row number. If your range starts at row 5, and the match is in row 7, MATCH returns 3 (the third cell in the range).