How to Find a Position with XMATCH in Excel

Get the position of an item in a list — exact, from the bottom up, or with wildcards — and feed it to INDEX.

You will learn to

• Find the position of an exact value with XMATCH
• Search from the bottom to find the most recent entry
• Use a wildcard match inside INDEX

How it works

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) returns the position of an item in a row or column. Unlike MATCH, it defaults to an exact match, so =XMATCH("T-1006",A2:A10) simply returns 6 if that ticket is the sixth in the list.

Two extra arguments make it far more flexible: match_mode 2 allows wildcards such as "Har*", and search_mode -1 searches from the bottom up, which finds the most recent entry in a chronological log. Wrap XMATCH in INDEX to return any column from the matching row.

=XMATCH(G2,B2:B10,0,-1)