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 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:
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 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...
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.
Dave Blake MAAT
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.