MATCH in Google Sheets

Returns the position of a value in a row or column. Use 0 as match_type for an exact match.

In Google Sheets

MATCH takes the same arguments in Google Sheets. The last one defaults to 1, the nearest smaller value in a sorted range, so write 0 for an exact match.

How to use MATCH

MATCH answers the question "which position?" Give it a value and a range, and it returns where the value sits in that range, counting from 1. You do not get the value back, only its place, like learning that a name is fifth on the guest list.

On its own, a position is good for checks. =ISNUMBER(MATCH(A2,F2:F20,0)) tells you whether an invoice number appears on another list at all. Its main job is to feed INDEX: MATCH finds the row, INDEX fetches the value from that row, and together they look up anything in any direction, including to the left, as in =INDEX(A2:A20,MATCH(F2,C2:C20,0)).

Nearly always, finish with 0 for an exact match. The default of 1 expects data sorted from small to large and returns the last value that is less than or equal to yours, which fits price tiers but not customer IDs. MATCH reads only a single row or column. With 0, text lookups accept wildcards, so =MATCH("Ber*",A2:A20,0) finds the first name that starts with Ber. The newer XMATCH is exact by default.

Syntax

MATCH(lookup_value, lookup_array, [match_type])
lookup_value
The value to find.
lookup_array
A single row or column to search.
match_type (optional)
0 exact match; 1 (default) largest value less than or equal, in ascending data; -1 smallest value greater than or equal, in descending data.

Examples

D1=MATCH("Pear",A2:A4,0)
ABCD
1FruitPrice2
2Apple4
3Pear6
4Plum5

In plain words: Find the position of Pear in the fruit list in A2:A4, only on an exact match.

D1=MATCH(5,B2:B4,0)
ABCD
1FruitPrice3
2Apple4
3Pear6
4Plum5

Common MATCH mistakes

  • MATCH returns a wrong position or #N/A in an unsorted list

    The last argument is missing, so the default of 1 applies, and that needs ascending data. Add 0 at the end: =MATCH(F2,A2:A20,0).

  • MATCH returns #N/A even though the value is in the list

    The values only look the same. A trailing space or a number stored as text is the usual cause. Clean the value with =MATCH(TRIM(F2),A2:A20,0), or use VALUE(F2) when the list holds real numbers.

  • INDEX and MATCH return the row next to the one I wanted

    The two ranges do not start in the same row. If the INDEX range starts in row 2, the MATCH range must too: =INDEX(C2:C20,MATCH(F2,A2:A20,0)). Including the header row in only one of them shifts the position by one.

  • MATCH returns #N/A when I select a whole table

    MATCH reads only one row or one column. Point it at the single column that holds the keys, such as A2:A20, not A2:C20.

Practice MATCH

Related functions