The guide · MATCH
What MATCH returns
MATCH doesn't return a value from the list. It returns where the value is: a number counted from the top of the range. In a list of runners written in finishing order, that position is the place each one finished.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])- lookup_value: what to look for. Point at a cell instead of typing it in.
- lookup_array: one column or one row to search.
- match_type:
0for an exact match.1(the default when you leave it out) and-1are for sorted lists.
Worked example
A2:A9 holds Priya, Omar, Lena, Tariq, Mei, Jonas, Sofia and Luca, in the order they finished. =MATCH("Sofia", A2:A9, 0) returns 7: Sofia is the 7th name in the range, so Sofia came 7th. The count starts at the top of the range, not at row 1 of the sheet. Sofia is on row 8, but the range starts on row 2.
Why the 0 matters
Leave the third argument out and MATCH does an approximate match. It assumes the list is sorted from smallest to largest, A to Z for text, and searches it quickly: it checks the middle, throws away the half the value can't be in, and repeats. On a list that isn't sorted, it can throw away the half with your value in it. You get a wrong position or #N/A, with no warning. On the race list, =MATCH("Sofia", A2:A9) returns 3.
| match_type | Finds | The list must be |
|---|---|---|
0 | exactly the same value | in any order |
1 or left out | the largest value that is less than or equal | sorted small to large |
-1 | the smallest value that is greater than or equal | sorted large to small |
What MATCH is good for
On its own: places and ranks, checking whether something is on a list with =ISNUMBER(MATCH(D2, A2:A9, 0)), or finding which column a heading is in. Inside INDEX it becomes a lookup that works in any direction: that's the next challenge, INDEX and MATCH.
Common mistakes
- Leaving out the 0. Fine on sorted numbers, unreliable on anything else.
- Starting the range at the header. With A1:A9, the header is number 1 and every position is one too high.
- Not locking the range. Filled down, A2:A9 becomes A3:A10 and the first name drops out. Use
$A$2:$A$9. - Expecting a row number. MATCH gives the position inside the range. It's the same as the row number only when the range starts on row 1.
FAQ
- What does the MATCH function do in Excel?
- It returns the position of a value in a range. =MATCH("Sofia",A2:A9,0) returns 7 when Sofia is the 7th cell of A2:A9.
- What does 0 mean in MATCH?
- It asks for an exact match. Without it, MATCH assumes the list is sorted and can return a wrong position, or #N/A, on a list that isn't.
- Why does MATCH return #N/A?
- With 0, the value isn't in the range: check the spelling and look for extra spaces. Without 0, the list may not be sorted, and the search skipped past the value.
- Is MATCH case-sensitive?
- No. "sofia" and "Sofia" match the same cell.
- What is the difference between MATCH and XMATCH?
- XMATCH, in Excel 365 and 2021 or later, does an exact match by default. MATCH works in every version of Excel but needs the 0.