XMATCH in Excel: how to use it
XMATCH gives the position of a value in a list, like MATCH, but finds an exact match by default and can start at the bottom. Works in Excel 2021 and 365.
Try the first exercises for freeIn the lesson Excel 365 for advanced users
What does XMATCH do?
XMATCH looks up a value in a list and gives the position it's in: 1 for the first row of the list, 2 for the second, and so on. MATCH does that too, but XMATCH looks for an exact match straight away, without an extra argument.
With the third argument you look for the nearest smaller or larger value, even in a list that isn't sorted. With -1 as the fourth argument, Excel starts at the bottom, so you find the last time something appears. XMATCH works in Excel 2021 and 365.
When do you use XMATCH?
- Which position a product is in, in the list: =XMATCH("Monitor",B2:B5).
- Looking something up in any direction, together with INDEX: =INDEX(C2:C5,XMATCH(E1,B2:B5)).
- The last time a name appears in a list: =XMATCH(E1,A2:A100,0,-1).
- The discount that goes with an amount, in a table with limits: =INDEX(B2:B5,XMATCH(E1,A2:A5,-1)).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =XMATCH("Monitor",B2:B5)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
XMATCH | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | XMATCH |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
lookup_value | What you're looking for, such as a name, or a cell containing that name. | "Monitor" |
lookup_array | The column or row Excel searches. | B2:B5 |
[match_mode] | Optional: 0 or nothing looks for an exact match. With -1, Excel otherwise takes the next smaller value, with 1 the next larger one, and with 2 you can use wildcards such as *. | left out |
[search_mode] | Optional: 1 or nothing searches from top to bottom, -1 from bottom to top. | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
XMATCH step by step
E1 contains =XMATCH("Monitor",B2:B5). Monitor is in the third position in B2:B5. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1 and type XMATCH with a bracket.
=XMATCH( - Type what you're looking for, in quotation marks, and a comma.
=XMATCH("Monitor", - Select the column Excel searches, B2 to B5.
=XMATCH("Monitor",B2:B5 - Close the bracket and press Enter: Monitor is in position 3.
=XMATCH("Monitor",B2:B5)
More examples
Together with INDEX
E1 contains =INDEX(C2:C5,XMATCH("Monitor",B2:B5)). XMATCH gives 3, and INDEX reads the third price.
The last time
D1 contains =XMATCH("Lucy",A2:A5,0,-1). Lucy is in positions 1 and 3. With -1, Excel starts at the bottom and finds position 3.
The next smaller value
D1 contains =XMATCH(650,A2:A5,-1). 650 isn't in the list. With -1, Excel takes the next smaller limit, 500, in position 3.
Where it often goes wrong
- You get the position in the list, not the row number of the sheet. If your list starts in row 2, row 2 is position 1.
- It doesn't work in Excel 2019 or older. Use MATCH with 0 as the third argument there.
Practise this in the lesson Excel 365 for advanced users. The first exercises are free.
Frequently asked questions
What's the difference from MATCH?
Without a third argument, MATCH looks for an approximate match in a sorted list. XMATCH finds an exact match by default, can search from the bottom up and finds the nearest value even in a list that isn't sorted.