Dear Analyst Episode 17: Excel introduces new feature XLOOKUP
Podcast: Play in new window | Download
Subscribe: Spotify | TuneIn | RSS
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
or0
in yourVLOOKUP
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:
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)
Other Podcasts & Blog Posts
In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:
- The Changelog: Episode #352 – The Pragmatic Programmers with Andy Hunt and Dave Thomas
- This Week In Startups: Episode 954 – Gentry Underwood
No comments yet.