XMATCH does what MATCH does, with fewer traps. The default is an exact match, it can search from the bottom of a list, and it supports wildcards. It is available in Microsoft 365 and Excel 2021 or newer.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
- match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
- search_mode: 1 first to last (default), -1 last to first.
Basic example
Names in A2:A100:
=XMATCH("Priya", A2:A100)
No need to type 0 at the end, as you must with MATCH.
Find the last time something appears
Set search_mode to -1 to search from the bottom:
=XMATCH("Priya", A2:A100, 0, -1)
This is the easy way to find the most recent entry for a customer in a log.
Wildcards
Use match_mode 2 to find the first name starting with Pri:
=XMATCH("Pri*", A2:A100, 2)
Use it with INDEX
=INDEX(C2:C100, XMATCH(F1, A2:A100))
This is the same idea as INDEX MATCH, just shorter and safer.
XMATCH or MATCH?
If everyone who opens the file has Microsoft 365 or Excel 2021 or newer, use XMATCH. If the file goes to someone with Excel 2016 or older, stay with MATCH.