Goal: Show users some practical applications of the Excel function, XMATCH

This is a follow up to an earlier video that explored the new XLOOKUP function. On this occasion, we will compare the new XMATCH function with the MATCH function, and see it’s practical applications in spreadsheets.

XMATCH versus MATCH – Return_Array

First let’s use the MATCH function, which most Excel users are familiar with and return the index number for Delta. The MATCH for Delta should return 4.

We will now road-test the XMATCH to again find the Delta index number of 4.

Both MATCH and XMATCH have a lookup_array syntax, however XMATCH contains a match_mode and search_mode syntax. The match_mode options are like in XLOOKUP – we will use Exact match in this instance.

The search_mode variables are also the same as in XLOOKUP. The match_mode will use the Exact match option. Additionally, the search_mode will apply the Search first-to-last variable

We should again return 4 in the above screenshot, as the match value for Delta in the second application of this cross MATCH function.

Comparison of the syntax for MATCH, relative to the first above use of the XMATCH function

Here is a slight alternative to the above X formula. Instead, we will select Exact match or next smaller item for the match_mode. The search_mode option will be the same – first-to-last. As you can see both approaches to the XMATCH formula return the same result. A value of 4.

We will now use the MATCH formula on a horizontal array of data. We return an index number of 6 for the Gross Margin value in this selected range.

Let’s now employ the XMATCH function

Like the earlier example, we will use the Exact match option for match mode, and the first-to-last variable for the search_mode

We again tweak the match_mode in XMATCH and return the same result, by selecting Exact match or next smaller item for the match_mode

We can realise the same result of 6

Both MATCH and XMATCH have a lookup_array syntax, however XMATCH contains a match_mode and search_mode syntax

The match_mode and search_mode options are like in XLOOKUP.

Further information

Here is the example workbook that shows you how to use the XMATCH function

SpreadsheetVault – XMATCH practical uses (solution).xlsbDownload