Excel function · INDEX and MATCH
MATCH
Finds where a value sits in a list and returns its position as a number.
What it does
MATCH looks for a value in one row or column and tells you its position: 1 for the first item, 2 for the second, and so on. It doesn't return the value itself, which is why it is usually wrapped in INDEX.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,
Arguments
- lookup_value
- What to look for, usually a cell.
- lookup_array
- The single row or column to search.
- match_type
- 0 for an exact match. 1, or leaving it out, finds the largest value that is less than or equal in a list sorted smallest first; -1 does the opposite for a list sorted largest first.
Example
Kai is on the guest list. At what position?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Guest | Look for | Position | |
| 2 | Ana | Kai | 3 | |
| 3 | Ben | |||
| 4 | Kai | |||
| 5 | Lia |
The formula in D2:
=MATCH(C2,A2:A5,0)Result: 3
Kai is the third name in A2:A5, so the result is 3. The position counts from the start of the range, not from row 1 of the sheet.
Common mistakes
- Leaving out the 0. The default is an approximate match on a sorted list, so on an unsorted list it can return a wrong position without any error.
- Searching a range with several columns.
MATCHneeds one row or one column. - Expecting the value back.
MATCHreturns a position; useINDEXto turn it into a value.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | MATCH | , |
| Deutsch | VERGLEICH | ; |
| Français | EQUIV | ; |
| Español (España) | COINCIDIR | ; |
| Español (México) | COINCIDIR | , |
| 日本語 | MATCH | , |
| 한국어 | MATCH | , |
| 繁體中文 | MATCH | , |
| ไทย | MATCH | , |
| ខ្មែរ | MATCH | , |
| Tiếng Việt | MATCH | ; |