XMATCH function
XMATCH returns the position of a value in a list. It is an upgraded MATCH with exact match as the default and options for search direction and wildcards, so wrap it in INDEX to get a value.
Example
Read it as a sentence: find the position of Candle in B2:B7, 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
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Arguments in [square brackets] are optional.
-
lookup_value(required) - Value to find.
-
lookup_array(required) - Row or column to search.
-
match_mode(optional) - 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
-
search_mode(optional) - 1 first to last (default), -1 last to first, 2 or -2 binary search.
Returns: The position of the match.
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 |
|---|---|---|
=XMATCH("Candle",Items!B2:B7) | 3 | Exact |
=XMATCH(35,Data!J1:J5,1) | 4 | Next larger |
=XMATCH(35,Data!J1:J5,-1) | 3 | Next smaller |
=XMATCH("Smoking",Items!C2:C7,0,-1) | 5 | Last to first |
=XMATCH("c?ndle",Items!B2:B7,2) | 3 | Wildcard |
=XMATCH("^Cl",Items!B2:B7,3) | 5 | match_mode 3 with regex pattern |
=XMATCH("Zither",Items!B2:B7) | #N/A | Not found |
=XMATCH(1,Items!B2:B7,3) | #N/A | match_mode 3 |
The Items sheet these formulas read
B
C
1
Item
Category
2
Boxer cartridge
Tools
3
Briar pipe
Smoking
4
Candle
Household
5
Cigar
Smoking
6
Clay pipe
Smoking
7
Commonplace book
Papers
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 is exact, unlike MATCH.
- Returns a position, so wrap in INDEX to get a value.
Where it works
- Excel
- XMATCH arrived in Excel 2021. Older versions do not have it.
- 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 .