Xlookup is the newer, cooler, smarter guy who just moved in next door, and this is why he is as good as people seem to think he is! Available only to Microsoft365 (formerly Office365) subscribers, this new function effectively replaces VLOOKUP, HLOOKUP, and the infamous INDEX/MATCH statements in one incredibly user-friendly package. It even has built in error handling!

Each of the most popular looking functions has its own benefits and drawbacks, but when designing the Xlookup function Microsoft evidently decided it was time to create the (almost) perfect lookup function and do away with many of the issues of its predecessors.

Let’s run through a few test examples to see exactly where each function shines:

Our excellent data set

In our first example we found the first instance of a specific date and returned the payment type of the transaction it is associated to.

Each of our three competitors fared well and the most intimidating formula was that of the Index/Match functions. The one most people are comfortable with is the fan favourite VLOOKUP. Our newest function gracefully handles the task in a very logical and legible format, something VLOOKUP falters on.

Here is where VLOOKUP struggles, and XLOOKUP shines. If we want to return a value from a column to the left of our search column (without moving the columns around each time!) then we had no choice but to use INDEX/MATCH prior to the existence of XLOOKUP. Now, without any added complexity, we are able to return values from any column we want.

Now that we have disqualified VLOOKUP from the ‘Best Function Ever’ awards, we need to address the error in the room. Yes, I am talking about the #N/A error. The one which appears every time we misspell a search term, or are looking for the ghost of data’s past. Without any explicit error handling being called each formula will output the same error message.

When we wanted to advise a user that the thing they have been searching for doesn’t exist, we have always used an IFERROR function to catch those situations. With XLOOKUP, that is no more! Our new function has a built-in parameter for error handling. User friendliness really has been the name of XLOOKUPs game, no other functions have this level of simplicity when considering the power it also holds.

For those who are faint of heart this next example might be worth skipping! 

(Just kidding but it is more complex)

With all the basics being covered, the biggest advantage INDEX/MATCH had over VLOOKUP was the ability to perform a lookup on multiple criteria without an intense workaround. While it is possible to have a VLOOKUP with multiple criteria, for each additional criteria we add the formula grows exponentially longer. INDEX/MATCH suffers a similar fate, but while the length of the formula grows at a much slower pace, at a glance it looks much more complex.

XLOOKUP removes the need for the helper columns normally required in a VLOOKUP situation, and incorporates them into the arguments it takes in. While this might take some getting used to, the layout of these formulas is not only more condensed than its predecessors, but when combined with named ranges is much more legible than some of its alternatives!

For all its versatility and improved functionality, XLOOKUP looks to be the latest and greatest Microsoft could come up with regarding lookup and reference functions. The one unfortunate downside is that it is only available to Microsoft365  subscribers, and those who are configured to receive monthly or bi-annual updates have access to it as of the original posting date of this article. Even users who purchased the latest single payment solution of Office 2019 will not have access to the newly announced functions.

I for one, have enjoyed the extra versatility provided by XLOOKUP, XMATCH, and the other Microsoft365 exclusive functions. Despite this, I understand why some users, and businesses, are hesitant to upgrade to a subscription model. This means that until companies decide to go all-in on Microsoft's annual subscriptions, INDEX and MATCH will still reign supreme over the newly introduced powerhouse that is XLOOKUP.

For those of you interesting in practising your lookup skills and testing out the examples provided you can download my Excel sheet HERE