Lookup and reference
XMATCH function playground
Returns the position of a value in a list. It matches exactly by default and can search from the bottom.
The playground runs in your browser. Nothing you type is sent anywhere.
How XMATCH works
XMATCH looks for the value in one row or column and returns its position: 1 for the first item, 2 for the second, and so on.
Unlike MATCH, it matches exactly unless you ask otherwise, so you don't need a 0 at the end. match_mode -1 or 1 finds the next smaller or larger item, and the list doesn't have to be sorted.
On its own a position is rarely the answer. Pair it with INDEX: INDEX returns the item at the position XMATCH finds.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| Argument | What it means |
|---|---|
lookup_valuerequired | The value to find. |
lookup_arrayrequired | The single row or column to search. |
match_modeoptional | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard (* and ?). |
search_modeoptional | 1 first to last (default), -1 last to first. |
Examples
| Formula | What it does | Result |
|---|---|---|
=XMATCH(F1,A2:A9) | Position of the product typed in F1. | 2 |
=XMATCH("Keyboard",A2:A9,0,-1) | Search from the bottom: the last Keyboard in the list. | 7 |
=XMATCH("*phone*",A2:A9,2) | Wildcard match: the first product containing "phone". | 5 |
=XMATCH(100,B2:B9,1) | Position of the smallest price that is 100 or more (exact or next larger). | 5 |
=INDEX(B2:B9,XMATCH(F1,A2:A9)) | Pair with INDEX to return the price of that product. | $250.00 |
Common mistakes
Older ExcelExcel 2019 and earlier don't have XMATCH and show #NAME?. Use MATCH with 0 as the last argument instead.
Searching a blocklookup_array must be a single row or column. A range like A2:C9 returns #VALUE!.
Expecting the valueXMATCH returns a position, not the item. Wrap it in INDEX to get the value itself.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.