Introducting the new XLOOKUP and XMATCH functions
Part two 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 (this blog)
  3. The XMATCH Function

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 XLOOKUP function

This function - now in beta testing - will make your life easier by eliminating problems associated with the VLOOKUP function.

What's wrong with the old VLOOKUP function

The much-loved (and much-abused) VLOOKUP function has been the favourite of Excel users for many years:

VLOOKUP formula

Here the formula showing the calories for any pizza is =VLOOKUP( C2, B9:D30, 2, 0).  That is, look up the value typed into cell C2 in the left-hand column of the range B9:D30, and return the value in the second column in this range).

 

This formula suffers from several drawbacks:

  1. It's flaky.  Insert a column between B and C above and your VLOOKUP function will give the wrong result, as you'll no longer be returning the value of the second column in the lookup table.
  2. It relies on the thing you're looking up (in this case the pizza name) being in the left-hand column of the table.
  3. It's hard to copy (to get the pizza type as Romana I had to copy the formula, then change the column returned from the table from 2 to 3).

The new XLOOKUP function

To get round this problem Microsoft are introducing the XLOOKUP function, which has the following syntax:

=XLOOKUP( The thing being looked up, the column being looked up against, the column(s) containing the data to be returned )

The easiest way to understand this is with a couple of examples! 

Case study - changing the column order

Here's a formula which will give a pizza's calories even when the pizza name isn't in the first column of a table:

Example of XLOOKUP

This formula looks up the pizza name (here Calabrese) in the range of pzza names (C9:C30).  This will determine that this is the 4th out of 22 entries.  The formula then returns the 4th value in the Calories range B9:B30.

 

If you've used the INDEX/MATCH function combination before you'll recognise that this is combining both functions into one.

Case study - filling in multiple values

In this example, we want to fill in the calories and the pizza type with a single formula:

Multiple values

The new "dynamic arrays" introduced last year into Excel allow you to create formulae like this to populate multiple cells.  This formula will find that a Calabrese pizza is the 4th out of 22 pizzas in range B6:B27, and return the 4th row from the two-dimensional range C6:D27.

 

 

Some things, though, haven't changed: the XLOOKUP function still uses the fourth argument to determine whether you're using an exact match or not.

This blog has 3 threads Add post
03 Oct 19 at 14:13

This will be a great development and I can already see many use cases and spreadsheet improvements.

Have Microsoft, however, thought about adding an alert dialog box that pops up when people are attempting to use this to calculate pay adjustments/tax returns/financial calculations and should probably be using something more robust! Far too many systemised spreadsheets out there that fail too often.

Of course, maybe it would be easier for Microsoft to just pop up a suggestion to get some training from Wise Owl before attempting anything too complicated...

03 Oct 19 at 23:14

We'll email Bill to suggest this.  Something along the lines of the old paper-clip assistant.

24 Sep 19 at 16:47

Thanks for the mention. It is also important to note that whilst you have stated the 4th argument (match_mode) is required (exact match, approx match and others too), exact match is now the default argument (finally), this differs from VLOOKUP where the default is approximate match. Therefore if the argument is omitted you will get an exact match. 

Regards

Dave Blake MAAT

24 Sep 19 at 20:22

Thanks - that is a really important difference, which I hadn't realised.  Also worth seeing this announcement from Microsoft, which I think has just come out.

24 Sep 19 at 12:41

Dear Andy,

Thanks for Introducting the new XLOOKUP and XMATCH functions but in my Excel sheet (I am using excel 2016) XLOOKUP isnot showing when i type it in. 

Regards

Pankaj Negi

 

24 Sep 19 at 13:29

As the blog says, it's only released at the moment to people who have signed up for previews of the software - you will have to wait a bit for it to be available within the vanilla version of Excel, sadly!