MATCH function
MATCH finds where an item sits in a list and returns its position number, not the value itself. It is usually combined with INDEX. Use 0 as the match type for exact matches.
Example
Read it as a sentence: find Candle in the Item column, B2:B7, and give its position in the list, using an exact match, which gives 3.
Excel shows 3
B
1
Item
2
Boxer cartridge
3
Briar pipe
4
Candle
5
Cigar
6
Clay pipe
7
Commonplace book
Items
Syntax
MATCH(lookup_value, lookup_array, [match_type])
Arguments in [square brackets] are optional.
-
lookup_value(required) - What to find.
-
lookup_array(required) - A single row or column to search.
-
match_type(optional) - 0 exact, 1 largest value not above it (sorted ascending, default), -1 smallest not below it (sorted descending).
Returns: The position of the match within the array.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=MATCH(35,{50,40,30,20,10},-1) | 2 | Descending, type -1 |
=MATCH("~*",{"a","*"},0) | 2 | Escaped wildcard |
=MATCH(9.5,{0,1,5,50},1) | 3 | Site: approximate, ascending bands |
=MATCH(9.5,{50,5,1,0},-1) | 1 | Site: -1 on a descending list |
=MATCH("Candle",Items!B2:B7,0) | 3 | Exact |
=MATCH("candle",Items!B2:B7,0) | 3 | Case-insensitive |
=MATCH("Zither",Items!B2:B7,0) | #N/A | Not found |
=MATCH(5,Data!J1:J5,1) | #N/A | Below first |
The Items sheet these formulas read
B
1
Item
2
Boxer cartridge
3
Briar pipe
4
Candle
5
Cigar
6
Clay pipe
7
Commonplace book
Items
The Data sheet these formulas read
A cell that starts with = holds that formula.
J
1
10
2
20
3
30
4
40
5
50
Data
Watch out for
- Default match_type is 1, which needs sorted data; use 0 for exact matches.
- Returns a position, not the value.
- Supports wildcards only with match_type 0.
Where it works
- Excel
- MATCH is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .