MATCH finds where a value appears in a single row or column. Its result is a position inside the search range, which you can use directly or pass to INDEX.
Use 0 for an exact match in an unsorted list. The other match modes are useful for ordered thresholds, but depend on the correct sort direction.
MATCH function syntax
=MATCH(search_key, range, [search_type])- search_key: the number, text, date, or reference to find.
- range: one row or one column, not a rectangular table.
- search_type: 0 for exact; 1 for ascending approximate, the default; -1 for descending approximate.
The Google MATCH reference describes the function arguments.
Example data
Enter this dataset starting in A1. Leave cells marked “leave empty” empty; type any displayed formula as a formula. Keep a separate blank area for the results.
| Product | Jan | Feb |
|---|---|---|
| Keyboard | 120 | 140 |
| Mouse | 90 | 100 |
| Monitor | 180 | 200 |
| Speaker | 75 | 85 |
| Webcam | 110 | 130 |
The examples below use this dataset unless the formula supplies its own values. Array results need enough empty cells to expand. The formulas use commas as argument separators. Your spreadsheet locale may require semicolons.
Find an exact position
Monitor is the third item in A2:A6, so MATCH returns 3. The result is relative to this range, not the worksheet row.
=MATCH("Monitor",A2:A6,0)Result: 3
Text matches are case-insensitive. Lowercase mouse finds the same product as Mouse.
=MATCH("mouse",A2:A6,0)Result: 2
Find a threshold in a sorted list
For an ascending list, match type 1 finds the position of the largest threshold at or below 75. Here that threshold is 70.
=MATCH(75,{0;50;70;90},1)Result: 3
For a descending list, match type -1 finds the smallest threshold at or above 75. Here that threshold is 90.
=MATCH(75,{100;90;70;50},-1)Result: 2
Return a value with INDEX and MATCH
MATCH locates Monitor, then INDEX retrieves January sales from the same position in B2:B6. This approach can also return a column to the left of the lookup labels.
=INDEX(B2:B6,MATCH("Monitor",A2:A6,0))Result: 180
Find a row and column together
Use one MATCH for the product and another for the month heading. INDEX returns the value at their intersection.
=INDEX(B2:C6,MATCH("Monitor",A2:A6,0),MATCH("Feb",B1:C1,0))Result: 200
Locate the largest value or its cell address
MAX supplies the largest January value. MATCH returns its position; tied maximums return the first exact match.
=MATCH(MAX(B2:B6),B2:B6,0)Result: 3
Add the source range’s starting row, minus one, to turn a relative position into a worksheet row. ADDRESS then produces A4.
=ADDRESS(MATCH("Monitor",A2:A6,0)+ROW(A2)-1,COLUMN(A2),4)Result: A4
Match a text pattern
The asterisk matches any following characters. M* finds Mouse first, even though Monitor also starts with M.
=MATCH("M*",A2:A6,0)Result: 2
Other Google Sheets articles you may also like