Table of Contents
XLOOKUP Explained
XLOOKUP is a new Excel function announced in 2019 and broadly released in 2020 that significantly improves some of the most common lookup and reference tasks Excel users encounter on the job.
If you’re familiar with VLOOKUP and index match you will find XLOOKUP to be an absolute revelation. So how does it work?
Imagine you have an employee data set:
Prior to XLOOKUP, if you wanted to identify Elen Bates’ compensation dynamically – such that a user can select Elen’s last name from a dropdown, you would likely build a VLOOKUP function as follows:
To make the formula work, you’d have to identify the exact column index number – in this case “5” – and you’d have to make sure that the table array starts with the Last Name column.
Of course this made VLOOKUP very brittle – adding columns would always break the formula without additional work to make the formula dynamic:
XLOOKUP vs VLOOKUP
XLOOKUP resolves all of this by replacing the table array parameter with 2 new array parameters – the lookup array and the return array. This simple and elegant change makes everything so much less brittle and so much more dynamic:
While the XLOOKUP function has 5 parameters, only the first 3 are required – the lookup value (in our case the Bates last name), the lookup array (in our case the array containing the Bates last name) and the return array (in our case the array containing the compensation data).
We’ll explain the other 2 in a separate post, but the vast majority of use cases only require the first 3.
Related Topics: Check out our free mini course on Excel’s new super function =LAMBDA(), the function that let’s users create their own custom functions, without the need for Excel VBA.
XLOOKUP vs Index Match and Offset Match
If you have used Excel much in the past, you’re probably familiar with another fix for the problems we just described relating to VLOOKUP and HLOOKUP – namely the index / match combination.
Of course, index match worked great – and continues to work – but in comparison to XLOOKUP now adds more complexity than required. It pains every fiber of my being to retire index / match since it’s done so much heavy lifting for me on the job, but here you can see old reliable offset match doing the same thing XLOOKUP is doing, albeit with a much more complex (and error prone) formula:
