by Wyn Hopkins

How and why to use XLOOKUP

It’s August 2019 and we get our first glimpse of this glorious new function.
In May 2016 I raised a UserVoice request to create a simpler and safer version of VLOOKUP and INDEX MATCH.
As of May 2020 many Office 365 users now have it, and everyone else on 365 will get access to it in the July 2020 update release.

The idea received a lot of votes and gradually momentum built until it was significant enough to really interest the Excel Team. Joe McDaid, Senior Program Manager on the Excel team, ran with the idea and some clever folk got involved and began working on this solution.

XLOOKUP is here

=XLOOKUP( CellValueToLookFor, ColumnOrRowToLookIn, ColumnOrRowToReturn, [other optional things] )

It will eventually put an end to the debates of which is better VLOOKUP or INDEX MATCH. I say “eventually” because while those of us on Office 365 subscriptions will hopefully get access to this in the next 6 months or so, those folk who bought Office 2019 or older versions Excel will never get to use this unless they change to an O365 subscription.

“XLOOKUP is easier to write
and harder to get wrong”

XLOOKUP is easier to write than both formulas, and harder to get wrong. Users often misunderstand or omit optional match / search criteria or hard-code numbers into the VLOOKUP formula which then causes unintended errors when columns are inserted or deleted.

Let’s Demonstrate

Watch the video and/or read on…

(XLOOKUP was released to all channels of Office 365 in July 2020, it will never become available in Excel 2019 or earlier versions)

LOOKING LEFT (Simpler)

One of the well known problems of VLOOKUP is that it can’t look to the left.

OK that’s not entirely true: You could use CHOOSE{1,2} inside a VLOOKUP

=VLOOKUP(F8,CHOOSE({1,2},tblBlue[Food], tblBlue[Amount to Left]),2,FALSE)

But that is not a nice formula to remember or explain. With XLOOKUP it’s now simple…

= XLOOKUP( F8, then select the column to look in, then select the column to return )

=XLOOKUP( F8, tblBlue[Food], tblBlue[Amount to Left] )

That’s it! SIMPLE!

DEFAULT EXACT MATCH (Safer)

One risk with VLOOKUP and INDEX MATCH is if you forget to specify an Exact Match by using FALSE or 0.

In this screenshot the formula doesn’t have a FALSE on the end

It gives the right answer -for now!

But look what happens if I change Cherry to Grapefruit

The approximate match gives us the result for Banana!

No issue with XLOOKUP as the default behaviour is EXACT MATCH.

SAFER!

ALSO REPLACES HLOOKUP

The formula above in yellow results in 3, as it returns the value from the range P7:AA7

For most users that’s it. Simpler and Safer than VLOOKUP and INDEX MATCH

Cherry on top…

As of 30th October 2019 we also have an Optional [IF NOT FOUND] parameter as the 4th option largely thanks to people voting via the link below!

=XLOOKUP( A1, C1:C100, B1:B100, “Item is Missing”, [other optional things] )

=XLOOKUP( A1, C1:C100, B1:B100, “Please Add Item to Lookup list”, [other optional things] )

**** This section was in my original article and I’m leaving it in for posterity*** While it seems wrong to raise any comment about this awesome new function, I’d also like to have an optional parameter that allows me to avoid having to use a separate IFNA or IFERROR wrapper when a match isn’t found. Bill Jelen has a UserVoice item here… Please vote…

Optional Parameters (more advanced use cases):

IMPROVED APPROXIMATE MATCH

We’ve always been able to use VLOOKUP without a FALSE when we are genuinely looking for approximate matches, such as nearest value to the value you are searching for.

e.g closest result lower than 23 will give us Banana

But…

The source table MUST be sorted in ascending number order and we can only find an exact match or the next smallest item.

“XLOOKUP DOESN’T REQUIRE THE SOURCE TO BE SORTED for approximate
match to work properly”

XLOOKUP does not require the source table to be sorted for approximate match to work.

=XLOOKUP(F8, tblData[Amount], tblData[Name], ,-1) gives me Lisa and +1 gives Jackie.

AWESOME! Note the two commas after [Name]. This is because I haven’t entered the optional [if not found] message.

or including the optional [if not found] parameter

WILDCARD SEARCH IS NO LONGER THE DEFAULT (SAFER)

If you select the value of 2 rather than -1 or 1 then you can do a Wildcard search. In the image it finds the first item beginning with A, whereas without setting it as option 2 then you would only get a result if it could find A*

If I wanted to look for a specific missing character I would search for A? rather than A*

Note: INDEX MATCH and VLOOKUP perform this wildcard action by default, which could result in unintended consequences. e.g. imagine this scenario where you are actually searching a column where items have * or ? in the item names

SEARCH LAST TO FIRST OPTION

Finally, there is one last optional parameter, Search from last to first.

By default XLOOKUP searches down a list and finds the first occurrence. However, by tagging an optional -1 at the end you can find the last occurrence (i.e. it starts searching from the bottom up)

=XLOOKUP(H6,tblOne[FirstName],tblOne[Output 3],,,-1) results in 6,000. Without the -1 at the end we would’ve returned 4,000 (this first match for Betty looking from top down)

There are 2 additional options under this final parameter relating to Binary Search. Don’t worry about these unless you’re a devil for speed as this option might (and I repeat might) be a bit quicker than the default options under the new calculation engine. For me and 99% of others we’ll never use this option.

Multi Criteria Lookup

One last tip. The new Dynamic Array version of Excel which is starting to role out among Monthly Channel users allows us to do Multi Criteria lookup without using Control Shift Enter.

=XLOOKUP( C12&D12, tblGrey[FirstName]&tblGrey[LastName], tblGrey[Start Date] )

This results in 4th August being returned