=MATCH(E2,A2:A6,0) returns the position of the value in E2 within A2:A6: 1 for the first cell, 2 for the second, and so on. The 0 asks for an exact match. MATCH returns a number, not the value itself, which is why it is usually paired with INDEX.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Position | |
| 2 | Apple | Fruit | $1.20 | Bread | 4 | |
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Bread is the fourth cell of A2:A6, so F2 shows 4. The count starts at the first cell of the range, not at row 1 of the sheet: change the range to A1:A6 and the answer becomes 5. Type Kiwi in E2 and MATCH returns #N/A.
MATCH syntax and match types
=MATCH(lookup_value, lookup_array, [match_type])
| match_type | Finds | The list must be |
|---|---|---|
0 | The first value exactly equal to lookup_value. Wildcards allowed for text. | in any order |
1 or omitted | The largest value less than or equal to lookup_value. | sorted ascending |
-1 | The smallest value greater than or equal to lookup_value. | sorted descending |
The default is 1, not 0. A MATCH without its third argument on an unsorted list of names does not complain: it returns a position that may belong to another row. Write the 0 every time you look up text, codes or IDs.
Approximate match: which band a value falls in
With match type 1, MATCH answers "which band": the position of the last threshold the value has reached. Here a score of 75 has passed 0, 60 and 70, so it is in band 3, grade C.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Min score | Grade | Score | Band | Grade | |
| 2 | 0 | F | 75 | 3 | C | |
| 3 | 60 | D | 59 | 1 | F | |
| 4 | 70 | C | 90 | 5 | A | |
| 5 | 80 | B | 100 | 5 | A | |
| 6 | 90 | A |
59 is still below 60, so it is band 1 (F). 90 is an exact match for the last threshold, and 100 is above it, so both land in band 5 (A). The thresholds have to stay in ascending order: that is what lets MATCH stop at the right place.
Match type -1 is the mirror image for a list sorted from largest to smallest. It returns the position of the smallest value that is still greater than or equal to the lookup value: with sizes {50;25;12;5} in a column, =MATCH(18,D2:D5,-1) returns 2, the 25 size, the smallest that holds 18.
Wildcards and case
MATCH ignores case, and with match type 0 it accepts the wildcards * (any characters) and ? (exactly one character). For a case-sensitive match, compare the list with EXACT and look for the first TRUE.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Category | Price | Starts with "C" | 3 |
| 2 | Apple | Fruit | $1.20 | Lower case "milk" | 5 |
| 3 | Pear | Fruit | $1.50 | Exactly "milk" | #N/A |
| 4 | Carrot | Vegetable | $0.80 | ||
| 5 | Bread | Bakery | $2.40 | ||
| 6 | Milk | Dairy | $1.10 |
"C*" matches Carrot, position 3. "milk" finds Milk at position 5, because case is ignored. The EXACT version compares each name with "milk" case by case, finds no TRUE and returns #N/A. Change A6 to milk and it returns 5. In Excel 2019 and older, confirm the EXACT formula with Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac); in Excel 2021 and Microsoft 365 Enter is enough. A tilde makes a wildcard literal: "~*" looks for an actual asterisk.
Check if a value is in a list
MATCH returns a number when it finds the value and #N/A when it does not. ISNUMBER turns that into TRUE or FALSE, which is a quick "is this in the list" test.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ordered | In stock? | In stock | |
| 2 | Pear | Yes | Apple | |
| 3 | Kiwi | No | Pear | |
| 4 | Milk | Yes | Bread | |
| 5 | Plum | No | Milk | |
| 6 | Bread | Yes |
Pear, Milk and Bread are in D2:D5, so they show Yes. Kiwi and Plum show No. COUNTIF($D$2:$D$5,A2)>0 gives the same answer; MATCH stops at the first hit, COUNTIF counts every one.
MATCH, XMATCH and INDEX
MATCH is half of the classic lookup =INDEX(C2:C6,MATCH(E2,A2:A6,0)), explained on the INDEX and MATCH page. Excel 2021 and Microsoft 365 add XMATCH, which defaults to an exact match, can search from the last item up, and handles "next larger" without a descending sort. MATCH still works everywhere, including in files that must open in Excel 2019.
Practice: position of an employee
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Start year | Name | Position | |
| 2 | Ana | Sales | 2019 | Dev | ||
| 3 | Ben | Support | 2021 | |||
| 4 | Cara | Sales | 2018 | |||
| 5 | Dev | Finance | 2022 | |||
| 6 | Eli | Support | 2020 | |||
| 7 | Fay | Finance | 2023 |
Your turn: In F2, return the position of the name in E2 within the list in column A.
Frequently Asked Questions
What does MATCH return in Excel?
A position, not a value: =MATCH("Bread",A2:A6,0) returns 4 when Bread is the fourth cell of A2:A6. Pair it with INDEX to get the value at that position from another column.
What is the difference between match type 0, 1 and -1?
0 finds an exact match in any order. 1 (the default) finds the largest value less than or equal to the lookup value and needs the list sorted ascending. -1 finds the smallest value greater than or equal to it and needs the list sorted descending.
Is MATCH case-sensitive?
No. =MATCH("milk",A2:A6,0) finds Milk. For a case-sensitive match use =MATCH(TRUE,EXACT(A2:A6,"milk"),0), which needs Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) in Excel 2019 and older.
How do I check if a value exists in a list with MATCH?
MATCH returns #N/A when the value is missing, so test whether it returned a number: =ISNUMBER(MATCH(A2,$D$2:$D$5,0)) gives TRUE or FALSE. Wrap it in IF for your own text.
