The Excel XMATCH Function was introduced in 2021 to enable excel users perform advanced matching and lookup operations. The XMATCH function is a successor to the MATCH Function, it is a more robust and flexible function compared to its predecessor. It allows users perform approximate matches with more flexibility, and it also provides options to choose between binary and linear search methods. In today’s post, we would be touching on the must know for the XMATCH function and how to deploy to your spreadsheet models. Let’s take a brief look at the syntax and argument, then we move on from there.
Syntax & Argument
The syntax of the XMATCH function is as follows:
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode], [result_mode])
Quite a handful of arguments contained in this syntax. There are 5 arguments in total, 2 required and 3 optional arguments. let’s see what they mean below:
lookup_value: This is simply the value to lookup within the lookup array.
lookup array: This represents the range of cells that contains the values to be looked up.
match_mode: This is the first optional argument that specifies the match mode using numbers ranging from -1 to 1, where 0 represents an exact match, -1 represents a smaller or equal match and 1 represents a larger or equal match.
Search mode: This is the second optional argument that specifies the search mode which could be linear or binary. The number 1 is for a binary search in a sorted array whilst 2 is for a linear search in an unsorted array.
result mode: This is also an optional argument whose purpose is to specify the result mode with 0 or omitted for an exact match, -1 for a smaller match and 1 for a larger match.
Usage Notes
- The XMATCH function returns the numeric position of the lookup value as a result.
- The default match mode for the XMATCH function is an exact match as opposed the default approximate match of its predecessor the MATCH function.
- The XMATCH function can find the next larger item or the next smaller item.
- The XMATCH function doesn’t require sorting when performing an approximate match unlike the MATCH function.
- The XMATCH function can be used to perform a reverse search (search from last to first).
- The XMATCH function has a binary search feature which allows for faster searching however you would need to sort the data first.
XMATCH vs MATCH
The XMATCH and MATCH functions have similar syntax for a lot of related operations. You only need to replace the MATCH with XMATCH in your formula and it works just as fine. Examples are as follows:
Exact match syntax
=XMATCH(value,array,0) // exact match for the XMATCH function
Approximate match syntax
For approximate matches we have the next smallest and next largest values which both the MATCH and XMATCH functions are able to handle.
Next Smallest Value
=XMATCH(value,array,-1) // next smallest value for XMATCH, no sorting required
Next Larger Value
=XMATCH(value,array,1) // next largest value for XMATCH, no sorting required
Match mode
There are basically 4 types of match modes with the XMATCH function, let’s take a look at them below:
| Match Mode | Match Behaviour |
|---|---|
| 0 (default) | This will return an exact match and a #N/A if there is no match. |
| – 1 | For exact match or approximate next smaller item match |
| 1 | For exact match or approximate next larger item match |
| 2 | For Wildcard matches (? *) |
Search Mode
The search mode allows users specify how the XMATCH function should perform the search for a specified value in an array.
| Search Mode | Behaviour |
|---|---|
| 1 (default) | Searches from the first value to the last value |
| -1 | Reverse search from the last value to the first |
| 2 | Binary search with values sorted in ascending order |
| -2 | Binary search with values sorted in descending order |
The binary search modes have an edge over the default search and reverse search mode when it comes to speed. The flaw however is that you would need to sort the data first, taking you back to how the MATCH operation functions.
XMATCH Function Examples
Finally in the example section, and for this section we have an interesting data set. Let me briefly introduce you to it. In our data set, we have a list of the 7 continents on planet earth, and to the right of the column, we have each continent’s estimated population and land mass.
Before you put me on blast that I got one of the continents wrong, do remember that the continent most people call Australia is actually called Oceania. Yes, Australia is the main part, but we use Oceania to include also the islands surrounding Australia, else they won’t be included in the traditional 7-continent model.
Exact Match
For this section, we would use the XMATCH function to return the position number of one of the continents in our list.
The formula for returning the position of Asia is as follows:
Approximate Match
For our approximate match, we would be looking at the next smaller item match or next larger item match. Let’s start with the next smaller item match.
Next Smaller item match
Now let’s build on our previous section example and include 2 lookup values. The lookup values will be random land masses and we would use the XMATCH function to return an approximate next smaller item match for one and an approximate next larger item match for the other in our next section.
For the number 13 in cell F6, we would use the XMATCH function to return an approximate match of a smaller item in our data set. If we do it correctly, we should get the position of the land mass of the continent with a landmass that is lower and closest to 13. Our formula is as follows:
Next larger item match
Our next example is on approximate next larger item match. We would be using the XMATCH function to find the position of the Land Mass immediately greater than our lookup value (27).
Our formula for our approximate next larger item match is as follows:
INDEX and XMATCH
The XMATCH function can be combined with the INDEX function to retrieve a value in one column based on an exact match to a lookup value that is present in another column. The way it works is similar to how the INDEX and MATCH function combination works. Let’s take a look with our previous data set.
In our example, we need to match and retrieve the continent and land mass for one of the population numbers in our data set. Let’s start with matching and retrieving the continent, we need to select the column containing the continent information (column B) as our array argument for the Index function, then we use the XMATCH function for the row argument to locate the relevant row. Let’s see the formula below:
Next is the Land Mass, for this we only need to change the array argument range from column B to column D. Let’s see the formula below:
XMATCH with Wildcards
In our match mode section, we stated the number to use as our match mode argument when using XMATCH function with wildcards, in this section, we would illustrate with an example, let’s begin shall we.
In our example, we need to return the position of the cell containing postal code starting with FL. If you recall in our match mode, we stated that the number 2 should be used as the match mode argument for Wildcard matches. Now Let’s see the complete formula below:
Multiple Criteria
In this section, we are taking a look at applying the XMATCH function to a multiple criteria scenario. Our data set is on the sales information of some merchandizing items. We would use the XMATCH function to return the position of the row that matches all the criteria we specify.
As you can see from the above, we need to return the position of the item which is a Hoodie, with a medium size and red in colour. To achieve this, we use the formula below:
Explaining the formula
The XMATCH formula in this example is different from the usual syntax of the formula. This is because we need to change the XMATCH Function to an array formula and starting with 1 tells excel you have one or more criteria to confirm if true or false.
=XMATCH(1,B8:B17,0) // Change to array function by starting with 1
The section formula in our example changes to an array function by starting with 1. We however still have work to do as the formula in its current state would result in an error. The next step would be to wrap each criteria after the “1,” in brackets and connect with an asteriks operator (*). So that each criteria would be (criteria1=range1)*(criteria2=range2).
For each criteria and range pair, excel would check each row in the range for those that match the criteria and return a TRUE if yes and False if no, then it moves on to the next criteria and range pair. At the end of processing all the criteria and range pairs, excel matches rows that returned True, to get a single row that returned True for all logical tests.
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
Only the 8th entry for each row returns True for all cases, excel then calculates it to be the row number and returns 8 as the position.
Binary Search
The binary search mode is used to run matches faster than the regular mode however there is a catch which is that the data must be sorted in ascending or descending order. If your data is sorted in ascending order, you are to use the value 2 for search mode whilst if the data is sorted in descending order you are to use the value -2. Let’s take a look at the generic syntax below:
=XMATCH(value,array,0,-2) // binary search Z-A
