COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
Introducting the new XLOOKUP and XMATCH functions
Part three of a three-part series of blogs

Microsoft have finally recognised the limitations of the VLOOKUP function, and are rolling out the XLOOKUP function to supersede it, as well as the useful (if less significant) XMATCH function.

  1. Two new functions in Excel (XLOOKUP and XMATCH)
  2. The XLOOKUP function
  3. The XMATCH Function (this blog)

Posted by Andy Brown on 13 September 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The XMATCH Function

Once you've got over the unfortunate name (I wonder if it will get renamed before its release?) this function is a good idea, simplifying the MATCH function by removing one of its arguments.

An example of the MATCH function

Staying with our pizza theme, the MATCH function below returns the position of a value within a list:

Answer Formula
To get the number 4 ... ... you could use this formula.

So in this case Calabrese is the 4th item in the range B7:B28.

You could then combine this with the INDEX or OFFSET functions to pick out, say, the calories for this pizza.

Replacing this with the XMATCH function

The problem with the above example is that 99.99% of the time the value for the third argument is 0.  Here's the syntax of the MATCH function:

The match type

The match_type argument is nearly always set to 0, to denote that we want to find an exact match for what we're looking for.


Given this, Microsoft are introducing the XMATCH function, which allows you to miss out this redundant 3rd argument, giving:

Simpler formula

The formula is simpler, because it's shorter.



Right - time to clear my browsing history after researching the XMATCH function!

This blog has 0 threads Add post