Today is most definitely one of the most exciting days of this year for all Excel users out there. Today the endless discussions about whether people should use VLOOKUP or INDEX-MATCH are finally OVER! I will admit that I thought it should have been over back when Power Query Joins came around but maybe those were too far out for people to relate to them as a VLOOKUP substitute. But now there is something as easily relatable as the VLOOKUP itself since it’s also a function. And from now on, it’s the only function you are going to use to lookup things. Dear Excel Users, I (or rather Microsoft) give you XLOOKUP. Before we dive into XLOOKUP, let’s pretend we are Simon Sinek and look at the WHY.

WHY Do We Need a New Lookup Function?

Simple. It’s for the same reasons that had people searching and using for the INDEX-MATCH alternatives. I guess we are all very familiar with the issues:

• you can only “lookup” to the right
• you will only find the first match
• if you forget the 0 or FALSE as the last argument you might not even get the desired result since the default was an approximate match
• the “column serial number” or rather the third argument of VLOOKUP was always a disaster waiting to happen when data was dynamic
• you could never look for the last matching row
• Looking for the nearest greater value was nearly impossible if those were not numbers and you didn’t know the LARGE function

It needs to be said, there were many workarounds for the pain points listed above, but in my experience people just thought that it shouldn’t be that hard…

And now, here is the answer to all these troubles, the XLOOKUP function. And if you are thinking something like: “If this will replace the VLOOKUP function, what will replace the HLOOKUP then?” The answer might surprise you, it’s XLOOKUP :). So it can look in any direction and that is why it is an X-LOOKUP not unlike the X-files which were also looking in all directions (the original series, not the “whatever that was” that came recently). So let’s take a look at the XLOOKUP function.

XLOOKUP syntax

=XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

lookup_value is exactly the same as it was with the VLOOKUP function

lookup_array is either data in a column or in a row. It’s where the XLOOKUP will look for the match (by default!) of the lookup_value

return_array is either data in a column or a row that doesn’t need to be a part of the same table as the lookup_array, but more importantly, doesn’t need to be “to the right” of the lookup_array and most importantly, is a reference to a range and will move with insertions of columns or rows!

and since you can see the remaining two arguments are in square brackets and are therefore optional, this is all you need. And once again, it will search for an exact match by default! Now let’s look at the remaining two arguments since the last one is what we were wishing for so long.

match_mode: the most important thing to understand here is that this argument goes way beyond what the TRUE and FALSE was with VLOOKUP. here are your options and their short explanation

• zero is your FALSE (and in case you didn’t know, you could use zero in VLOOKUP) and again (and for the last time 🙂 ) this is the default behavior
• 1 will return the nearest smaller item (only if there is no exact match!)
• -1 same as 1 but this will return the first larger item
• 2 allows you to do a simple wildcard match with the same rules that apply to use of wildcards to filter in Excel
  • ? replaces any one character
  • * replaces any number of any characters (number can be zero)

I hope you can already see the brilliance of this :), but it gets even better.

search_mode gives you the ability to either use 1 to search from first to last or -1 to search from last to first.

Don’t worry, I haven’t lost my mind, it’s supposed to be all bold, as it is that brilliant! And there is also an ability to do a binary search thrown in there but let’s not get into that.

Further reading

To find out where or better when you are going to get it, I would refer you to this website where you will also find the XMATCH function.

Original Excel Blog Post