There’s been a lot of clamour about the XLOOKUP formula. Does it ‘change everything’ in Excel, as some have claimed?  I’m sceptical.  But, it’s certainly worth exploring the possibilities …

 Download Your Excel Free Cheatsheet

First, let’s understand what it does!  What’s all the fuss about?

CLICK HERE TO WATCH THE VIDEO

Let’s deal with concept of ‘looking up’.  You’ll be familiar with this idea if you have used VLOOKUP or HLOOKUP in Excel.

So, what’s wrong with VLOOKUP?  A weakness of VLOOKUP is that it only returns data from columns *to the right* of the lookup column.  Let’s take a second to explore this limitation …

CLICK HERE TO DOWNLOAD THE FILE

Think about a telephone directory.  You would imagine somebody’s telephone number to be positioned to the right of the surname; that seems natural, at least to Westerners like me. 

But, what if data you wanted were positioned *to the left* of the name?  It might be a reference number, for example – this person is the 2135th person in the telephone directory.  We would not be able to access that column with VLOOKUP because it only returns data to the right of the lookup column.  Get the idea?  Good!

You might be thinking: we can easily solve that using MATCH and OFFSET or MATCH and INDEX – since combining two formulae allows us to return data to the left of the target column.  You’re right.

You mean XLOOKUP only offers an alternative to existing approaches?  It isn’t actually a new capability at at all?  It doesn’t change the game?

Quieten down!

Before getting into this discussion, let’s look at the mechanism.  Like VLOOKUP, XLOOKUP asks for a ‘lookup value’ first.  What value do you want to look up?  Whose name are we looking for in the telephone directory?  The ‘magic’ happens in the next 2 components of the formula: lookup array and return array.

Unlike VLOOKUP that only looks up in the first column of a range (then looks to the right of that column to return a value,) XLOOKUP can look up in any column of a dataset.  Yes, any column!  More than that, the value returned can be *to the left* of the lookup column.  This is what XLOOKUP is asking for in its third component – return array.

Now, that’s pretty cool.  I recommend trying out XLOOKUP in the starter file that comes with this video.  I very much like its user-friendliness and flexibility.

But – does it ‘change the game’?  And should you learn it?

 Download Your Excel Free Cheatsheet

I’m not sure.  This function of the formula is only a substitute for MATCH / OFFSET or MATCH / INDEX, it’s not a new capability in Excel.  The formula has other functions (such as returning values from multiple columns) that could be described as unique contributions. 

Should be using it?  It really depends on how you are using Excel.  If you are using Excel for yourself – go ahead!  But, if you are creating files for colleagues or clients, just bear in mind the formula is only available in Excel 365.  Yes, the formula does not exist in Excel 2019!

This is why, for me, this kind of new formula is interesting but has to be treated with scepticism.

And for me, finally, there is a more fundamental point.  I want you to think – are you an Excel technique collector?

Excel technique collectors love learning new stuff in Excel.  They follow all the Excel YouTubers.  They are in the Excel insider programme.  They follow all the Excel MVPs and they love sharing the latest LinkedIn post about a new Excel future.  Why not?  It’s new and everybody else is talking about it …

You see, more techniques does not mean more impactful Excel work.  In fact, an obsession with techniques can be detrimental; because, it distracts and detracts from what really matters in Excel.  I have met many ‘technique collectors’ who really struggle with analytical work.  Despite knowing literally all the formulae!

So, what really matters?  Good question!  And it’s difficult to explain in a paragraph, but I’ll try.

There is a set of ideas that is more important.  First: real-world applications.  You have to understand what people use Excel for – data input, data modelling, data analysis and data automation.  The application should inform your choice of technique, not the latest formula release.  Further, a set of ‘metaskills’ is essential to succeed in Excel.  One of these metaskills is the ability to debug formulae when they go wrong, and I talk about the others on the channel all the time.

The key is to view Excel as a skill that can only be learned over time.  Sorry to sound boring!  But, to a minority, that idea will intrigue and excite … 
What, learning XLOOKUP won’t instantly improve my Excel practice?  Not necessarily, and certainly not if your client is using Excel 2007 :=)

Don’t be a technique collector.  Be a value creator.