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 22nd 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:C27.

 

 

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 0 threads Add post