XMATCH

What it does

XMATCH returns the position of a value in a row or column, like MATCH. It looks for an exact match unless told otherwise, and it can search from the last item backwards, which finds the most recent entry in a log.

Syntax

=XMATCH(lookup_value; lookup_array; [match_mode]; [search_mode])

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;

Arguments

lookup_value

What to look for, usually a cell.

lookup_array

The single row or column to search.

match_mode

0 (the default) for exact. -1 or 1 accept the next smaller or larger item when there is no exact match, and 2 allows the wildcards * and ?.

search_mode

1 (the default) searches from the first item; -1 searches from the last.

Example

Ana appears twice in the sign-in list. Where is her latest sign-in?

The formula in D2:

=XMATCH(C2;A2:A5;0;-1)

Result: 3

With search mode -1 the search starts at the bottom. The last Ana is the third name in A2:A5, so the result is 3; searching from the top would give 1.

Common mistakes

• Copying MATCH's third argument. In MATCH 0 means exact and 1 means approximate, but in XMATCH 1 means "exact or next larger".
• Forgetting that the position still counts from the top, even when searching from the bottom.
• Using it in a file that must open in old Excel versions, which don't have it and show #NAME?.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• XMATCHModule 6 · INDEX and MATCH

More from this module

• INDEX
• MATCH

All functions