How and why to use XLOOKUP

It’s here! In May 2016 I raised a UserVoice request to create a simpler and safer version of VLOOKUP and INDEX MATCH.

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.

Now, at the end of August 2019, we have our first glimpse of this glorious new formula.

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”

Let’s Demonstrate

(As of 1st September 2019 I’m using the Insider Fast Channel of O365, not all Insider Fast releases currently have XLOOKUP yet but it should be rolled out gradually to all Insiders by end of September)

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..

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”
 

As you can see below when I randomly sort the green table the VLOOKUP fails whereas XLOOKUP(F8, tblGreen[Amount], tblGreen[Food], -1) gives me Banana and +1 gives Cherry. AWESOME!

 

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

 

The formula is the same in both yellow cells below (see the optional 2 parameter at the end), but one is looking at Grap? while one is looking at Grap*. The ? represents just one missing character, while * represents a series and therefore picks up the value for Grapefruit.

 

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(D12,tblGrey[FirstName],tblGrey[Start Date],,-1) results in 6th August. Without the -1 at the end we would’ve returned the 4th Aug.

 

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

 

And even multiple outputs

 

There’s more examples to follow but that’s enough for now.

Stay tuned / connect / follow for future examples and updates.

Extra Info…

If you want more detail, or to find out about XMATCH check out these links..

XLOOKUP Release Blog

XLOOKUP Help

XMATCH Help

Business News article Tech scene: Spreadsheet users win as problem solver excels