XMATCH function

Returns the position that a given value appears at in the given lookup array, or returns an #N/A error if the value cannot be found. XMATCH(30, { 10, 20, 30 })XMATCH(30; { 10; 20; 30 }) returns 3, because 30 is the third item in the array. XMATCH(40, { 10, 20, 30 })XMATCH(40; { 10; 20; 30 }) returns an #N/A error, because 40 cannot be found.

Exact matches by default

XMATCH is a modern replacement for MATCH. By default, XMATCH tries to find an exact match for the value you supply, whereas MATCH tries to find an exact match or, failing that, the next smallest match.

As an exact match is typically what is needed, MATCH used correctly often needs a third parameter always set to 0, asking for an exact match only. XMATCH needs no such extra parameter when an exact match is needed.

Comparing XMATCH and MATCH features

XMATCH offers many features not offered by MATCH, including searches starting with the last item first, which is useful when there are duplicates and you prefer a value found at the end of the array to one found at the beginning. XMATCH can also use a binary search if the lookup array is known to be ordered, which is faster. Finally, XMATCH supports wildcards for text searching, making it possible to, say, find values that begin or end with a certain text string. (The wildcard "*at" matches both "cat" and "bat", for instance.)

MATCH has one feature not offered by XMATCH, though: the ability to determine a match using a formula fragment. Here's an example:

The formula above returns the position of the first element which is both odd and is greater than 10. Consider using this feature of MATCH instead of XMATCH if you need the extra flexibility and ease of use offered by formula matching.

XMATCH versus XLOOKUP

The return values of XMATCH and MATCH are typically used together with INDEX to return the found array element. Used this way, INDEX used with XMATCH or MATCH can fully replace the XLOOKUP function.

These formulas are equivalent:

Match modes

The third parameter to XMATCH is the match mode, which determines which array elements are considered a match.

Finding exact matches

Providing 0 as the match mode requests an exact match (which is the default). 0 can also be written as MatchMode.ExactMatchMode,Exact.

This formula returns 2, because 3 is the second element of the array:

This formula returns an #N/A error, because 2 cannot be found in the lookup array:

To check if a value is an #N/A error, use the ISNA function. This formula returns TRUE to indicate that the value cannot be found:

Finding exact matches or the next smallest value

Providing -1 as the match mode informs XMATCH that an exact match or the next smallest value should be returned. -1 can also be written as MatchMode.ExactOrNextSmallestMatchMode,ExactOrNextSmallest.

(This match mode is the default for the legacy MATCH function.)

Both these formulas return 2, because 3 is part of the lookup array:

However, these two formulas don't return the same result:

The first formula (using a match mode of 0) returns an #N/A error to indicate that 2 cannot be found, whereas the second formula (using a match mode of -1) returns 1. It does so because the element 0 is found at position 1, which is the next smallest element.

XMATCH can return an #N/A error even if the match mode is set to -1. Specifically, if there is no array element which is smaller than the sought value, #N/A is returned.

This formula returns #N/A, as no array element is smaller than the sought value, -10:

Finding exact matches or the next largest value

Providing 1 as the match mode informs XMATCH that an exact match or the next largest value should be returned. 1 can also be written as MatchMode.ExactOrNextLargestMatchMode,ExactOrNextLargest.

This formula returns 2, referencing the array element 3, which is the next largest array element in the array:

This formula returns #N/A, as 100 cannot be found in the array, nor is there a larger element:

Finding text matches with wildcards

Providing 2 as the match mode informs XMATCH that it should use wildcards when matching text. 2 can also be written as MatchMode.WildcardMatchMode,Wildcard.

Wildcards are special characters which match multiple text strings. This allows for flexible matching, also called "fuzzy" matching.

? matches any character, meaning that "ca?" successfully matches "cat" (as well as "car", "cab", "can" and "cap"). However, ca? does not match "card", because that word has four characters and the ca? pattern only matches words with three characters.

This formula returns 1, indicating that "ca?" matches "cat":

* also matches any character, but it can also match zero characters or many characters. "ca*" successfully matches "card" (as well as "care", "cat" and "ca").

This formula returns 1, indicating that "ca*" matches "card":

"ca*" would also match the other array elements, but 1 is returned as XMATCH searches from left to right by default (see below).

? and * can be combined in the same pattern. Colo*r ad*s are great? matches both "Color ads are great!" and "Colour adverts are great?".

Sometimes, the special characters ? and * need to be included as-is, and not have special meaning. To achieve that, precede each special character with a tilde ("~").

This formula returns 4, because only "ca*" matches:

In other words, to search for a verbatim "*" character, write "~*". To search for a verbatim "?" character, write "~?". Finally, to search for a verbatim "~" character, write "~~".

Search modes

The fourth parameter to XMATCH is the search mode. It determines which array elements need to be inspected and in what order.

Searching from left to right

Providing 1 as the search mode, which is the default, requests that the search be performed starting with the first element of the lookup array and ending with the last element. 1 can also be written as SearchMode.FirstToLastSearchMode,FirstToLast.

This formula returns 1, identifying the first array element 3, because XMATCH started its search at that element:

Searching from right to left

Providing -1 as the search mode requests that the search be performed starting with the last element of the lookup array and ending with the first element. 1 can also be written as SearchMode.LastToFirstSearchMode,LastToFirst.

For lookup arrays that contain duplicates, reversing the search order yields different results. Consider this formula:

The formula above returns 5, because XMATCH is instructed to start the search with the last element of the array. The last element is also 3 and is found at position 5.

Using binary search with elements sorted in ascending order

Providing 2 as the search mode instructs XMATCH to use binary search, for use with array elements ordered in ascending order (greater values appear last). 2 can also be written as SearchMode.BinarySearchAscendingSearchMode,BinarySearchAscending.

Using binary search is often much faster than searching through the elements from left to right or from right to left (known as a linear search), but only works if the array elements are known to be ordered. If you request a binary search with XMATCH and the array elements don't appear in order, the wrong result will likely be returned.

Searching from left to right or from right to left means that every element of the lookup array is (potentially) inspected in order for the XMATCH function to reach its verdict. That means that those search modes work with lookup arrays whose elements are not specially ordered.

Consider this formula:

The formula above causes XMATCH to inspect the element 50, 100, 150, 200, 250, 300 and 350 before finding 400. As the lookup array is ordered in ascending order (meaning that the smallest values appear first), it's much faster for XMATCH to use a binary search. As a result, the fourth parameter should be set to SearchMode.BinarySearchAscendingSearchMode,BinarySearchAscending or 2:

When performing the binary search, XMATCH takes advantage of the ordered nature of the lookup array. It starts in the middle, at 250, and quickly concludes that the sought value, 400, must be to the right of 250, as it's larger. XMATCH then discards all array elements to the left, and only looks at the elements 300, 350, 400, 450. It picks an element in the middle, 350, again concludes that the sought value is to the right of it, and then narrows the search to the elements 400 and 450. 400 is picked as the next candidate, and as it matches the sought value, its position is returned.

Performing a binary search can yield a big performance win, but only for arrays with thousands of elements. Also, if the lookup array is not known to be ordered, the results will be wrong, meaning that some care must be taken before asking for a binary search.

Using binary search with elements sorted in descending order

Providing -2 as the search mode instructs XMATCH to use a binary search with array elements ordered in descending order (greater values appear first). -2 can also be written as SearchMode.BinarySearchDescendingSearchMode,BinarySearchDescending.

This formula, with the array sorted in descending order, will return the correct result:

Returning multiple results

The sought value, match mode and search mode are usually single values and not arrays. However, if arrays are used for these parameters, XMATCH returns multiple values.

This formula returns 3:

This formula returns { 2, 3, NA() }{ 2; 3; NA() }, which are three results in a single array:

Consider this formula:

The formula above matches all the field values against the { 10, 20, 30 }{ 10; 20; 30 } array in turn, and returns an array containing one result for every field value.

(The Field1:Field100Field1:Field100 range is a short-hand way of expressing an array containing Field1 and Field100, as well as any fields that appear between them.)

The match mode and search mode can also use arrays. This formula returns the array { NA(), 3 }{ NA(); 3 }:

40 cannot be found in the array if the "exact" match mode is used, which explains why the first array element is #N/A. The second array element is 3, the position of 30 in the lookup array, which the "exact or next smallest" match mode finds.

Examples