Wall Street Prep

XLOOKUP in Excel: Why it Rocks and Comparison to VLOOKUP and Index Match

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:

Download this Excel File to tryout XLookup!

Submitting ...

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:

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.
Learn More

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.

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:

XLOOKUP in Action

Comments
guest
0 Comments
Inline Feedbacks
View all comments
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.