The MATCH function in Excel
MATCH doesn't return the value itself but its position: "Lena is the 3rd item in this list". That position is exactly what INDEX needs, which is why the two are used together.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
| Argument | What it means |
|---|---|
| lookup_value | The value to find. |
| lookup_array | A single row or column to search in. |
| match_typeoptional | 0 = exact match. 1 (default) = largest value less than or equal to, list sorted ascending. -1 = smallest value greater than or equal to, list sorted descending. |
Examples
1. Position in a column
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Department | Office | Position of Lena | |
| 2 | Rina | Sales | Tel Aviv | 3 | |
| 3 | Omar | Finance | Haifa | ||
| 4 | Lena | IT | Berlin | ||
| 5 | Tom | Sales | London |
Lena is the 3rd name in A2:A5. Use 0 for exact matches.
2. Position in a header row
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Department | Office | Column of Office | |
| 2 | Rina | Sales | Tel Aviv | 3 | |
| 3 | Omar | Finance | Haifa | ||
| 4 | Lena | IT | Berlin | ||
| 5 | Tom | Sales | London |
MATCH also searches across a row: Office is the 3rd header.
3. Approximate match in a sorted list
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Weight from (kg) | Shipping | Band for 6.3 kg | |
| 2 | 0 | 4.99 | 3 | |
| 3 | 2 | 7.5 | ||
| 4 | 5 | 12 | ||
| 5 | 10 | 20 |
With 1, MATCH finds the largest value up to 6.3, which is 5, the 3rd entry.
4. Two-way lookup with INDEX
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Department | Office | Tom's department | |
| 2 | Rina | Sales | Tel Aviv | Sales | |
| 3 | Omar | Finance | Haifa | ||
| 4 | Lena | IT | Berlin | ||
| 5 | Tom | Sales | London |
One MATCH finds the row (Tom = 4), another finds the column (Department = 2). INDEX returns where they cross.
Common mistakes
Leaving out match_type. The default 1 is an approximate match, which gives wrong positions in unsorted lists.
Fix: Use 0 unless you really want a band lookup on a sorted list.
Searching a multi-column range, e.g. A2:C5. MATCH returns #N/A.
Fix: Give it a single row or a single column.
Frequently asked questions
Does MATCH return the value or the position?
The position. Combine it with INDEX to get the value.
Can MATCH use wildcards?
Yes, with match_type 0: =MATCH("L*",A2:A5,0) finds the first name starting with L.
Related functions
- INDEXINDEX returns the value at a given row and column of a range.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.