Dear Analyst Episode 17: Excel introduces new feature XLOOKUP

New Excel feature alert! Probably one of the biggest announcements in a long time from the Excel world: a new function to supplant VLOOKUP and HLOOKUP. What is it you ask? The XLOOKUP function.

Summary of XLOOKUP benefits

I discuss some of the benefits, implications for new and existing users of Excel, and also some of the great comments in the announcement blog post. according to the blog post, VLOOKUP is the 3rd most used function in Excel behind SUM and AVERAGE!

Read more from the official Microsoft Excel announcement. The one and only Bill Jelen of MrExcel did a great writeup of the new XLOOKUP function. In short, here are some of my favorite improvements XLOOKUP brings to the traditional VLOOKUP and INDEX/MATCH functions for finding your data:

Defaults to exact match on the lookup (no more adding false or 0 in your VLOOKUP formulas)
Can insert columns into the range of data where you are doing the lookup without needing to change the column number of the data you want to return
Can look to the left of the range (this is already doable with INDEX/MATCH, however)
Performance improvements due to the fact you only need to select two columns instead of the whole range for the lookup

XLOOKUP in action

See the XLOOKUP function in action (from the blog post):

What people are saying

Some of my favorite comments from around the web on the new XLOOKUP function:

Today I discovered that #XLOOKUP is coming to Excel to effectively replace the VLOOKUP function. My life is complete. I will die a happy man.
— Jayesh Navin Shah (@jayeshnavin) August 31, 2019

#Excel is probably one of the first software that anyone learns when they start to look at the numbers. Apparently, the widely used #VLOOKUP function will have an heir to overshadow its glory: #XLOOKUP. https://t.co/7P4rBYca5t
— Cem Tekesin (@TekesinCem) August 30, 2019

What a great enhancement. This will be much easier to be taught to beginners – I’m SO looking forward to be able to use and teach this new function! Thank you very much for considering the suggestion.
Katharina_Schwarzer (blog post comment)

I forgot to offer my congratulations to the development team in my earlier post. For me, traditional Excel appears plagued by ‘cheap and cheerful’ techniques that provide partial solutions but are constructed so as ‘not to place demands upon the end-user’. All of the new dynamic array functions seem to have brought with them a noticeable greater degree of rigour. More thought appears to have gone into the planning, so all the use cases that one might reasonably expect seem to be catered for. Ultimately, this reduces the need for the ‘tips and tricks’ that provide workarounds or guidance where there is overlapping functionality. Great stuff!
Peter Bartholomew (blog post comment)