BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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:
- 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.
- It relies on the thing you're looking up (in this case the pizza name) being in the left-hand column of the table.
- 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:
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:
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:
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.