MATCH vs XMATCH
XMATCH is the modern MATCH: exact by default and able to search from the end.
| MATCH | XMATCH | |
|---|---|---|
| Available in | Every version | Excel 365 and 2021 |
| Default match | Approximate (1): you must add 0 | Exact |
| Find the last occurrence | No | Yes, with search mode -1 |
Syntax and example
MATCH
=MATCH(lookup_value, lookup_array, [match_type])
=INDEX(A2:A8,MATCH(F2,C2:C8,0))
Returns the position of a value within a row or column.
XMATCH
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
=XMATCH(D2,B2:B9)
Returns the position of a value in a list. It is the modern version of MATCH.
Which one to use?
With INDEX either works; XMATCH avoids the classic mistake of forgetting the 0.
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Position with XMATCH
XMATCH(value, list) returns the position of a value, with an exact match by default. A fourth argument of -1 searches from the end, to find the last time it appears.
In E2, find the turn in which the customer in D2 first came.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Turn | Customer | Customer | First visit | Last visit | ||
| 2 | 1 | Ana | Luis | ||||
| 3 | 2 | Luis | |||||
| 4 | 3 | Ana | |||||
| 5 | 4 | Sofía | |||||
| 6 | 5 | Diego | |||||
| 7 | 6 | Luis | |||||
| 8 | 7 | Ana | |||||
| 9 | 8 | Marta | |||||
| 10 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What is the difference between MATCH and XMATCH?
- XMATCH is the modern MATCH: exact by default and able to search from the end.
- When should I use MATCH or XMATCH?
- With INDEX either works; XMATCH avoids the classic mistake of forgetting the 0.