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
=MATCH(1"Pear",2A2:A4,30)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 2 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Find the position of 1Pear in 2the fruit list in A2:A4, 3only on an exact match.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 3 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
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 useVALUE(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, notA2:C20.