XMATCH function

The XMATCH function is a more modern version of the MATCH function, and is used to search arrays, by row or column, to find the position of a given value. It supports accurate and approximate comparisons, and wildcard characters can be used as a substitute for one (?) or more characters (*) during search. This is another of the new dynamic array features that is available exclusively to Office 365 subscribers as of February this year.

The syntax of this function is:

XMATCH (<value>,<string>,[<comparison mode>], [<search mode>])

and its arguments are:

• value – the value that we are trying to find
• string – the array that we search
• comparison mode – optional, 0 for exact match (default), -1 for exact match or lower, +1 for exact match or higher, 2 for wildcard search
• search mode – optional, +1 for search from the beginning (default), -1 for search from the end, 2 for binary search (ascending order) and -2 for binary search (descending order).

A little note about the last argument, binary search is an advanced array search algorithm that applies only to those sorted, in ascending or descending order. It is used when we have extremely large arrays so it is important for us to speed up the search.

The simplest way to use this function is when we want to find a given value in an array, stating only two arguments:

= XMATCH (G2,B2:B21)

If we only know part of the name of the value we are looking for we can specify it in the formula, but we also need to add the third parameter to which we set the value 2. For example, if we look for a colour which has a name that starts with letters “BLU” we will write the formula:

= XMATCH (G5,B2:B21,2)

We can also search in reverse order. If we look for the value of “GREY” in the sequence, starting from its end, we will write the formula:

=XMATCH(G8,B2:B21,,-1)