• SQL Spreads Blog
  • How to Use XMATCH: Guide and Examples

    Andy McDonald - Aug 18, 2022

    In a previous post, we looked at XLOOKUP, which is the new and improved replacement for one of Excel’s most popular functions, VLOOKUP. In this article, we’re going to look at XMATCH, which (you’ve guessed it) is a replacement for the MATCH function.

    As you’ll see, XMATCH doesn’t enhance the old MATCH function particularly – not compared to the way that XLOOKUP overcomes some very irritating limitations in VLOOKUP. The main reason for the update, therefore, seems to be to align XMATCH with XLOOKUP in terms of syntax and default settings.

    What is the XMATCH function?

    The XMATCH function searches for a specified item in a range of cells or an array and then returns the item’s relative position. There are a number of ways the function can be used, so we’ll look at each one in turn.

    Basic XMATCH

    The basic use of XMATCH is when we want to find the position of a value in a range.

    XMATCH Basic example As you can see, in its basic form, the XMATCH function requires two arguments: the thing you want to look up and the range in which to look for the thing. So, in our example we want the position (or index) that Tottenham finished in the English Premier League in the 2021/22 season, and the formula is:

    =XMATCH(M6,B6:B25)

    There are a couple of things to note about XMATCH in this example:

    • We’ve only used two arguments – there are actually 4 arguments in the XMATCH function; the other two allow you to specify the match mode and search mode (more on these later). Thankfully, Microsoft set the default values for these to be the most commonly used options so we can omit them in most cases such as the basic use case shown above.
    • XMATCH is case-insensitive – note that in the example I searched for ‘tottenham’ instead of ‘Tottenham’.

    XMATCH using the match mode option

    The match mode argument gives us a little bit of flexibility in how the match between the lookup value and the items in the lookup array is performed. In the basic example above, we simply used the default match mode which is an exact match, but this returns an error if there is no matching value in the lookup array.

    In the example below, we want to know what position a team finished in the league if they ended the season on 50 points. XMATCH with match mode As you can see the default match mode of 0 gives us an error. The other two options for this argument are to match against the next smallest value in the lookup array (-1) or the next largest value (1).

    In this use case, it is only relevant for us to match against the next smallest value, so this is the formula we need to use:

    =XMATCH(M6,M6:M25,-1)

    There are a couple of things to note about XMATCH in this example:

    • The values in the lookup array need to be in order – because we are interested in the position of a team based on their number of points, the teams need to be ordered in terms of points scored.
    • It is not always obvious which match mode to use – make sure you check carefully that the result returned is the correct one.

    There is one more option for match mode, and that is the wildcard match – we’ll take a look at this now.

    XMATCH using the Wildcard match mode

    You can use a simple wildcard match to look up the first record to match a wildcard expression.

    In the example below, I want to know the highest position that either of the two Manchester teams finished in the league, so I used a match_mode value of 2 and specified a lookup value of ‘Man*’ (‘?’ will also work as the wildcard)

    =XMATCH(M6,B6:B25,2)

    XMATCH Wildcard match

    Note that to return the actual team that finished highest (i.e. Man City), we can combine XMATCH with another function – INDEX. More on this below.

    XMATCH using the search mode option

    The addition of the search mode argument is the biggest change from the original MATCH function. It allows you to specify whether to start searching from the first record (the default behavior) or the last record.

    In the example below, we can get the lowest position that one of the Manchester teams finished in the league by using the ‘search from last’ search mode option, which is -1. XMATCH with search mode

    Uses of XMATCH

    The purpose of XMATCH is to return the position of an item in a range of cells. This is not often very useful in itself. It is far more common to want to know the value of an item at a particular position. To use our soccer example again, we might ask “Which position did Tottenham come in the league, and how many points did they get?”. In order to get this, we can combine the XMATCH function with the INDEX function.

    Let’s look at the syntax of the INDEX function:

    INDEX(lookup_array, row)

    So the INDEX function is saying “get the value in the lookup array at the specified row”. For our example, the lookup array is the column with the points in, and to get the row, we can use the XMATCH function from before. Doing this we get the following:

    =INDEX(J6:J25,XMATCH(M6,B6:B25))

    XMATCH with Index

    Here is another example that answers the question “Which of the two Manchester teams finished in the highest position?”

    XMATCH with index and wildcard

    Finally, let’s look at a common use case – looking up the current stock level in an inventory. In the example below we have a list of stock entries sorted by date ascending – in other words, new stock entries are added at the bottom of the range each time we do a stock take.

    The formula is the same INDEX/XMATCH as before, except that we use the ‘start from last’ search_mode option (-1).

    =INDEX(D6:D15,XMATCH(F7,C6:C15,,-1))

    The ‘start from last’ makes sure that we get the most recent entry in the cases where there is more than one record for the item we are looking up. XMATCH with index and search from last There is, however, an easier way to perform the above calculation, and that is to use XLOOKUP instead of XMATCH.

    XLOOKUP vs INDEX/MATCH

    Before XLOOKUP was introduced, people often used the INDEX and MATCH functions together to perform lookups where the limitations of VLOOKUP prevented its usage. The typical case was when the column containing the value you want to look up is located to the left of the column containing the return value – VLOOKUP can’t handle this. With the new capabilities of XLOOKUP, however, there really is no reason to use INDEX/XMATCH in place of XLOOKUP.

    Let’s look at how we could perform the same stock lookup calculation shown above using XLOOKUP instead of INDEX/XMATCH.

    =XLOOKUP(F7,C6:C15,D6:D15,"Not found",,-1)

    XLOOKUP vs INDEX/XMATCH The syntax for XLOOKUP is a bit simpler than using the INDEX/XMATCH functions together. It also has the bonus that we can include an ‘if not found’ message to display instead of “#N/A” when the lookup value is not in the return array.

    Summary

    The full syntax for XMATCH is shown below:

    =XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])

    Arguments

    • lookup_value – The lookup value.
    • lookup_array – The array or range to search.
    • match_mode[optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
    • search_mode[optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

    In most cases, you’ll only need to specify the first two arguments and let Excel use the default values for the other two. This makes the function slightly easier to use than the old MATCH function.

    XMATCH has also introduced the search_mode argument, which MATCH didn’t have. Before the new XLOOKUP function was introduced to replace VLOOKUP, people mainly used MATCH with the INDEX function to perform lookups which VLOOKUP couldn’t do. However, with the improvements to XLOOKUP, there is no real need to use XMATCH/INDEX.

    So, whilst XMATCH improves on the old MATCH function in some ways, it’s not the sort of function you’re going to need as often as, say, XLOOKUP.

    Leave a Reply


    No comments yet. Be the first!

Use Excel to update your data in SQL Server?

Try the SQL Spreads data management solution to update and manage your SQL Server data from within Excel.