BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Two new functions in Excel (XLOOKUP and XMATCH)
- The XLOOKUP function
- 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:
|
|
| 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 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:
The formula is simpler, because it's shorter.
Right - time to clear my browsing history after researching the XMATCH function!
- Two new functions in Excel (XLOOKUP and XMATCH)
- The XLOOKUP function
- The XMATCH Function (this blog)