What Is the MATCH Function?
MATCH searches for a specified value within a single row or column and returns its relative position (as a number). It does not return the value itself, just the position. This makes MATCH incredibly useful when paired with INDEX, because together they form a flexible lookup combination that can search in any direction, unlike VLOOKUP which only looks right.
Syntax
=MATCH(search_key, range, [search_type])
| Parameter | Description |
|---|---|
| search_key | The value you want to find. |
| range | A single row or single column to search within. |
| search_type | Optional. 1 for approximate match (range must be sorted ascending, the default), 0 for exact match, -1 for approximate match with descending sort. |
In practice, you will use 0 (exact match) most of the time.
Basic Examples
Finding a Product's Position in a List
| A | |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | Cherry |
| 4 | Date |
| 5 | Elderberry |
=MATCH("Cherry", A1:A5, 0)
Result: 3, because "Cherry" is the third item in the range.
Looking Up a Value in a Row
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | Q4 |
=MATCH("Q3", A1:D1, 0)
Result: 3. MATCH works horizontally too.
Approximate Match on Sorted Data
Suppose column A contains tax bracket thresholds in ascending order:
| A | B | |
|---|---|---|
| 1 | 0 | 10% |
| 2 | 10000 | 12% |
| 3 | 40000 | 22% |
| 4 | 85000 | 24% |
=MATCH(52000, A1:A4, 1)
Result: 3. The income of 52,000 falls between 40,000 and 85,000, so MATCH returns position 3 (the largest value that does not exceed the search key).
Advanced Examples
INDEX-MATCH — The Flexible VLOOKUP Alternative
This is the most common real-world use of MATCH. Suppose you have a product table and you want to look up the price for "Widget B":
| A | B | C | |
|---|---|---|---|
| 1 | Product | Category | Price |
| 2 | Widget A | Hardware | 12.50 |
| 3 | Widget B | Software | 29.99 |
| 4 | Widget C | Hardware | 8.75 |
=INDEX(C2:C4, MATCH("Widget B", A2:A4, 0))
Result: 29.99. MATCH finds that "Widget B" is at position 2 within A2:A4, then INDEX returns the second value from C2:C4. Unlike VLOOKUP, the lookup column does not need to be the leftmost column.
Two-Way Lookup with MATCH
To pull a value from a table based on both a row header and a column header:
=INDEX(B2:D4, MATCH("Widget B", A2:A4, 0), MATCH("Price", B1:D1, 0))
This finds the row for "Widget B" and the column for "Price," then INDEX returns the intersecting cell. It is essentially a flexible two-dimensional lookup.
Common Mistakes
- Forgetting to set search_type to 0. The default is 1 (approximate match), which requires sorted data. If your data is not sorted and you leave this out, MATCH will return wrong positions without any error.
- Searching a multi-column range. MATCH only works with a single row or single column. Passing a range like A1:C10 will cause an error. Use one dimension at a time.
- Off-by-one errors with INDEX. MATCH returns a position relative to the range you gave it, not the worksheet. If your MATCH range starts at row 5 and your INDEX range starts at row 2, the positions will not line up. Always make sure both ranges start at the same row.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.