Summary
There are many ways to extract values from data in Microsoft Excel, and two such examples are the MATCH and XMATCH functions. However, since Microsoft introduced XMATCH to Excel in 2021, I’ve ditched MATCH altogether, and in this guide, I’ll explain why you should too.
Since the XMATCH function is a modern upgrade from the MATCH function, it’s only available to those using Excel for Microsoft 365, Excel for the web, the Excel mobile and tablet apps, and one-off versions of Excel released in 2021 or later.
Microsoft Office 2024
Gain permanent access to Word, Excel, PowerPoint, and OneNote with the Office Home 2024 bundle. It isn’t a one-to-one replacement for a Microsoft 365 subscription, as it lacks some apps and does not include feature updates, but it’s a one-time purchase, so it’s ideal for those who just need the basics.
The MATCH Function in Excel
Microsoft Excel’s MATCH function tells you the position of an item in a range. The syntax is:
where
The MATCH function searches for the value (a) from the top downwards in the array (b)—it’s not possible to search from the bottom upwards.
In this example, typing:
into cell D2 looks for the word “Pears” in cells A1 to A4, and returns its position in this range.
MATCH is often used with INDEX to look up a value in an array, where MATCH determines the row number, and INDEX returns the value.
In a second example, typing:
into cell E2 returns a date from cells A2 to A24 (INDEX), where the row number is determined by the first exact match (as indicated by “0” in argumentc) between the value in cell D2 and the product IDs in cells B2 to B24 (MATCH).
A dollar symbol ($) placed before the letter in a cell reference locks the column (known as amixed reference) so that the formula still works correctly when copied across a row. References with dollar symbols placed before the columnandthe row areabsolute references, meaning they stay fixed, regardless of whether the formula is copied down a column or across a row.
The XMATCH Function in Excel
Microsoft Excel’s XMATCH function also tells you the position of an item in a range, but the defaults for omitted arguments differ, you may search from the bottom upwards, and you can include wildcard characters. The syntax is:
Even though argumentcis called “match type” in MATCH and “match mode” in XMATCH, they serve essentially the same purpose, but with different defaults and slightly different arguments.
into cell E2 returns the same result as the example above, where I used INDEX and MATCH, though I didn’t need to specify the match mode, as the default is an exact match. As a result, XMATCH requires you to input fewer arguments than MATCH in simple top-to-bottom lookup cases.
However, where XMATCH really trumps its predecessor is when you want to find thelastmatch in an array by searching from the bottom upwards. This is why XMATCH has one more optional argument (atod) than MATCH (atoc).
Here, typing:
into cell F2 returns thelatestdate from cells A2 to A24, because argumentdcontains “-1,” meaning the row number is determined by thelastexact match between the value in cell D2 and the product IDs in cells B2 to B24. The match mode argument (c) is omitted, since the default is an exact match, and that’s what we’re looking to return.
Another benefit of using XMATCH over MATCH is that the former lets you tell Excel you’re using wildcards in the search.
Here, INDEX and XMATCH are used to find the first and last dates a product from the garden department was sold.
Notice how, in cell D2, the asterisk (*) wildcard character is used to search for a product code that starts with the letters “GD” and contains any number of characters thereafter.
Then, in cell E2, I typed:
where “2” in the match mode argument (c) tells Excel that I’ve used a wildcard in the lookup value (a), and the search mode argument(d)has been omitted, as I wanted to search from the top downwards to find the first match.
If you don’t indicate in the match mode argument (c) that a wildcard is being used, XMATCH assumes that you’re looking for values containing * and ? in their own right, rather than treating these characters as wildcards.
In cell F2, I typed:
where, as well as adding the wildcard indicator for the match mode argument (c), I also typed “-1” for the search mode (d) to tell Excel to search from the bottom upwards.
To achieve the same outcome using the MATCH function, the match type must be specified as an exact match (“0”). However, since this is the default match mode for the XMATCH function, and because there’s a dedicated search mode for wildcard lookups, it’s a much more intuitive and cleaner process.
Another way to look up values in a table is by usingthe XLOOKUP function, an upgrade from the VLOOKUP and HLOOKUP functions. Like with XMATCH, XLOOKUP defaults to an exact match, and it lets you search from the top downwards and the bottom upwards. What’s more, XLOOKUP also lets you include an “if not found” argument, which saves the result from displaying “N/A” if the lookup value cannot be found in the array.
