Announcing XLOOKUP

Published 08-28-2019 09:00 AM 730K Views
Microsoft

March 31st Update
XLOOKUP is now available to all Office 365 Subscribers in the Monthly Channeland will be available to users in the Semi-Annual Channel starting in July. It will be available to users in Semi-Annual (Targeted) channel starting mid April 2020. 

 

We are excited to announce XLOOKUP, successor to the iconic VLOOKUP function. Before I describe XLOOKUP, I want to take a moment to reflect on VLOOKUP's incredible history and impact on spreadsheeting.

 

VLOOKUP has been with Excel from the beginning; it was included in Excel 1 for Macintosh released in 1985. For 35 years, VLOOKUP has been the first lookup function learned by Excel users and our 3rd most used function (after SUM and AVERAGE). In fact, it’s so fundamental to spreadsheeting that most users can recall the sense of achievement they felt when they first mastered VLOOKUP, myself included. 

Introducing XLOOKUP

XLOOKUP is named for its ability to look both vertically and horizontally (yes it replaces HLOOKUP too!). In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). Let’s consider its signature in the simplest form:


XLOOKUP(lookup_value,lookup_array,return_array)

 

  • lookup_value: What you are looking for
  • lookup_array: Where to find it
  • return_array: What to return

 

Let's see it in action:

 

158453_EXCEL_XLOOKUP_GIF_V08.gif

 


Advanced XLOOKUP variations

To perform advanced lookups, you can use XLOOKUP’s optional 4th and 5th mode arguments: match_mode and search_mode.

 

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

 

if_not_found allows you to specify what should be returned if no match is found

 

match_mode allows you to set the type of match you’d like to perform. The options are:

 

match_mode_sb.png

 

Use zero to perform an exact match. This is the default.
Use 1 or -1 to allow a match against the nearest smaller (or larger) item when there is no exact match. 
Use 2 to do a simple wildcard match where ? means match any character and * means match any run of characters

 

search_mode lets you configure the type and direction of search. The options are:


search_mode_sb.png

 

Use 1 or -1 to search from first-to-last or last-to-first.
Use 2 or -2 to do a binary search on sorted data. This is included for expert users only. 

 

Why release a new lookup function?

While VLOOKUP was widely used, it has several well-known limitations which XLOOKUP overcomes: 

 

  1. Defaults to an “approximate” match: Most often users want an exact match, but this is not VLOOKUP’s default behavior. To perform an exact match, you need to set the 4th argument to FALSE. If you forget (which is easy to do), you’ll probably get the wrong answer.
  2. Does not support column insertions/deletions: VLOOKUP’s 3rd argument is the column number you’d like returned. Because this is a number, if you insert or delete a column you need to increment or decrement the column number inside the VLOOKUP.
  3. Cannot look to the left: VLOOKUP always searches the 1st column, then returns a column to the right. There is no way to return values from a column to the left, forcing users to rearrange their data.
  4. Cannot search from the back: If you want to find the last occurrence, you need to reverse the order of your data.
  5. Cannot search for next larger item: When performing an “approximate” match, only the next smaller item can be returned and only if correctly sorted.
  6. References more cells than necessary: VLOOKUP 2nd argument, table_array, needs to stretch from the lookup column to the results column. As a result, it typically references more cells than it truly depends on. This could result in unnecessary calculations, reducing the performance of your spreadsheets.

 

What’s the future for VLOOKUP & HLOOKUP?

VLOOKUP and HLOOKUP will both continue to be supported by Excel. That said, we strongly recommend using XLOOKUP in favor of VLOOKUP and HLOOKUP because XLOOKUP is simpler to use and has none of the limitations listed above.

 

XMATCH

In addition to XLOOKUP we are also launching XMATCH which has a similar signature to XLOOKUP but returns the index of the matching item. XMATCH is both easier to use and more capable than its predecessor MATCH.

 

XMATCH(lookup_value,lookup_array,[if_not_found],[match_mode],[search_mode])

 

Learn More

You can learn more about XLOOKUP and XMATCH from these resources:

 

 

Availability Notes

XLOOKUP and XMATCH are now available to Office 365 Subscribers in the Monthly Channel and will be available to users in the Semi-Annual Channel later this year. 


To stay connected to Excel and its community, read the Excel blog posts and send us ideas and suggestions via UserVoice. You can also follow Excel on Facebook and Twitter

 

Joe McDaid (@jjmcdaid)
Program Manager, Excel

214 Comments
Occasional Visitor

XLOOKUP is a great new function! Thanks to the Excel team!

Frequent Contributor

Nice. There is a formula to use INDEX/MATCH to act as a VLOOKUP with multiple criteria:

 

=INDEX(Table1[Sale],MATCH(1,((Table1[Product]="a")*(Table1[Customer]=1)),0))

The same logic works with XLOOKUP, but is a bit easier to understand, and no need to enter as an array formula as the INDEX/MATCH required. (that might be before the new array engine though)

=XLOOKUP(1,((Table1[Product]="a")*(Table1[Customer]=1)),Table1[Sale])
ProductCustomerSale
a15
b210
c315


I was hoping the following would work, but just returns two cells of #VALUE

 

=XLOOKUP({"a",1},Table1[[Product]:[Customer]],Table1[Sale])

 

 

 

 

@Ed Hansberry , this shall work

=XLOOKUP("a"&1,Table1[Product]&Table1[Customer],Table1[Sale])
Frequent Contributor

Nice. I wouldn't have thought to concatenate different data types. Too much time spent in Power Query.

Valued Contributor

@xlpbi 

XMATCH only searches a single row or column so the simple answer to your question is 'no'.  To get 2D coordinates is much more complicated, as you suggest.  This gets worse if you wish to cater for the possibility of there being more than one "X", and more complicated still if you wish to return multiple coordinate pairs.

 

A formula you may like to play with is

= FILTER( IF({1,0}, I, J ), INDEX( D, I, J )="X", "Not found" )

where the indices I and J (named formulas) are defined to be

= 1 + QUOTIENT( SEQUENCE( ROWS(D)*COLUMNS(D)) - 1, COLUMNS(D) )

= 1 + MOD( SEQUENCE( ROWS(D)*COLUMNS(D)) - 1, COLUMNS(D) )

respectively.

Senior Member

@Peter Bartholomew 

Thanks - I am aware of XMATCH's limitation of being able to search in 1D Arrays - My Request was to Microsoft (Joe M)  to see if they would be willing to modify it to search 2D arrays after all it is still in a limited release - so I am sure they can make "improvements"

 

Your Formula however is brilliant !!! - very clever use of IF for the array concatenation and Index for creating a stacked table.

 

Sam

Occasional Visitor

Is it possible to return multiple answers or you still have to use an array formula?

@JorgeSamina , it's possible if you are on Dynamic Arrays Excel.

Occasional Contributor

Great!

The best for beginners is easy to understand.

 

Now can be searched from back and need not to use INDEX & MATCH combination.

 

Good Work

Any idea when this will come out as an Excel 2019 feature? (I know it's a premium Ofc365 feature for now when it finally is fully ready.)...  Looks pretty cool!..

Thanks,     Chet

Hi @positiveguy1960 

 

New formulas and functionality will never be added to Excel 2019.   You would need to buy Excel 2022 if there ever is such a thing.

 

When you buy Office 2019 you buy a one off static set of features.   With O365 you are subscribing to ongoing new features.

 

wyn

 

 

 

Valued Contributor

@Wyn Hopkins 

 

I didn't wish to move from a purchase model to a subscription model but the lure of decent array functionality (CSE works, but has all the user friendliness of a cornered rat) prevailed and I bought a O365 license in addition to Excel 2010 (somewhat old I realise).  

 

What troubles me now is that a permanent 'fork' has been created within Excel and there are many potential clients who will not be able to accept solutions built upon the use of dynamic arrays anytime in the foreseeable future.  They may  well not care, but I do!

 

I would at least like to see Excel 2019 purchasers offered an upgrade; they were sold short.

I’d agree that Microsoft didn’t do a good job of highlighting to people that Excel 2019 was a March 2018 ( I think) version of O365.  Normally people don’t notice, but Dynamic Arrays, Data Types and XLOOKUP are big deals and people are now realising the consequence of buying a one off purchase.

 

Comparability even within Office 365 is an issue now given the numerous release channels you can be part of.

Another way of looking at it is to say if you bought Excel 2010 you wouldn’t expect free upgrades to Excel 2013 or Excel 2016.

 

Frequent Contributor

There are people that don't want their apps to change, some enterprises notably. They buy the perpetual license so they know the software has a specific set of features that won't change.

 

But other than that, or someone that has no internet connection, I cannot think of any reason why anyone that uses office shouldn't buy Office 365. @Peter Bartholomew Office 2019 users weren't sold short. MS made it clear that Office 2019 would be based on the Office 365 build as of February 2018. It then took MS a few months to really bug test it and work out the kinks. It is essentially the semi-annual channel release based on that Feb 2018 build that would have come out around Aug/Sept.

Valued Contributor

@Ed Hansberry 

I used to work in an environment where all internet access was blocked with the exception of email and basic web queries, which were themselves the subject of regulation and active monitoring for compliance.  This was to ensure that no corporate information was held or processed on third-party servers.  In such circumstances I suspect a subscription model has little to offer the end user.

 

Something that troubles me from my participation within a number of Excel forums, is that there appears to be little awareness of the seismic change that is introduced by DAs.  Even when shown, many simply seek reasons why they should stay with the tried and tested techniques, with its plethora of tips & tricks and keyboard accelerators.  The chances of widespread adoption of DAs are not improved by denying access to a large proportion of Excel users; even if the developer is aware, nothing can be achieved if the client is not able to run the solution.

 

@Wyn Hopkins 

Buying new software in good faith (and perhaps a little ignorance) just before it becomes obsolete must be somewhat galling!  I have fielded a few posts in which the OP makes it clear that they are not happy.

 

My experience is that I can dispense with single-cell referencing (A1 or R1C1 notation) in its entirety by addressing all data as named arrays or structured references.  There is little requirement for the concept of relative referencing and, to the extent it is needed, the need is met by the '@' notation (array or table).  DA need not be an occasional party piece like CSE, it can provide the basis of the solution from beginning to end.

Occasional Visitor

Its now almost November 2019 - when will XLOOKUP arrive in Scotland

Frequent Contributor

Same time as it arrives in the US and other countries @Riven1. AFAIK, there are no plans for a staged international rollout.

Frequent Contributor

Nice! Hoping the new tweak to XLOOKUP hits soon. If Not Found is still the 6th argument here. Insider build 1911 12215.20006. Probably no way to do this reasonably, and I know this is the risk of being an insider, but it would be nice if Excel would warn us of a potential issue if it saw -1, 0, 1, or 2 in the 4th position now, which is an extremely unlikely use of the If Not Found parameter, and returning those values vs #NA could be pretty undesirable in some workbooks.

 

You managed a super smooth transition from SINGLE() to @ in dynamic arrays. This one could be a bit more likely to bite me.

Frequent Contributor

So if I want an XLOOKUP function to return data in a range (like a tax table lookup) and use -1 for the 5th option, and I want NA if my value is above/below the ranges provided (depending on my match type), I have to craft a formula like this?

=XLOOKUP(A4,Taxes[Start Bracket],Taxes[Tax Rate],,-1)

Seems if you are ever going to want the If_Not_Found argument, you'll be equally tedious in confirming the lookup type (exact, next smallest, next largest, etc), but if you want #NA() if no match found but you are using a range method, as is common with tax tables, you shouldn't have to have the If_Not_Found argument at all.

 

 

Valued Contributor

@Ed Hansberry 

Perhaps, if your tax table does not specifically include a zero-rated band, this could be provided as a value in the 'if not found' field using

= XLOOKUP( TaxableIncome, Taxes[Start Bracket], Taxes[Tax Rate], 0, -1 )

Frequent Contributor

There are other reasons beyond tax tables @Peter Bartholomew. Commission scales, discount tables, etc. I don't want 0. 0 suggests a match. I'm not saying I'll never use If_Not_Found, I absolutely will, but probably just as often, I want it to throw an error so I or the user will know either something is wrong, or that the table is in need of adjustment. If someone pastes in 150,000 from a web page into and it goes in as text, I want #NA, not 0. If someone busts our commission scale by selling more than the top bracket, I need #NA, not the wrong amount to pay the person, and certainly not zero.

 

Just saying there are lots of reasons to use something for the match mode other than 0, and it seems to me if you are going to use If_Not_Found, you'll be specifying match_mode to ensure you are thinking these scenarios through. Now that I think about it, even with match_mode = 0 (default), I'd be worried of the pasting text version of a number into a numeric field returning something besides an error.

Valued Contributor

@Ed Hansberry 

I had something a bit more targeted in mind.  In the case of the tax bands, 0% was intended to be the tax rate returned, not just some generic default to indicate an error has occurred.

 

In the case of commission rates it would be possible to work with the upper bounds, in which case the commission scale could be extended by the formula

= XLOOKUP( sales, Commission[Upper bound], Commission[Rate], topRate, 1 )

 

I got a surprise while testing my assertions, though.  I added a band with ">200000" as the upper bound, expecting it return an error; instead it returned the top bonus rate from the table perfectly happily.  I hadn't really thought of the case of non-numeric lookup values but this behaviour makes it possible to return NA() or text such as "Invalid sales figure" if the amount is entered as text, e.g.  "100000USD".

 

I have no idea what constitutes best practice but plenty of options seem to be available.

Valued Contributor

Out of curiosity, does the positioning of the error trap in

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

strike anyone as somewhat unexpected? 

I like the functionality it provides, but I would have expected to see it as the 'final parameter of last resort', to be used only once the options from the regular parameters have been exhausted.

Thoughts?

I pushed for the parameter to be in 4th position as did many others.  The thinking being the most common scenario for XLOOKUP will be an exact match.  Therefore to avoid most people having to do several empty commas = XLOOKUP( lookup_value, lookup_array, return_array, , , "Not Found")  it will be more user friendly for general users to have the if not found directly after the exact match syntax.

 

XLOOKUP is there to help the bulk of users with a simpler / safer version of VLOOKUP and INDEX MATCH but also has super powers tagged on the end for more advanced users.

 

I can see the argument for it being the very last parameter but the more advanced users will easily work it out and adapt.

 

Valued Contributor

@Wyn Hopkins  Fair enough, I can see the arguments both ways.  @Ed Hansberry's discussion had brought the match mode into play and that probably coloured my perception.  Another consideration is that the "Not found" parameter could be something far more complex than a text string; it could, for example, be a nested formula that builds and searches an alternative array using an embedded XLOOKUP.  I had thought of getting all the parameters of the outer XLOOKUP sorted before turning to the alternative formula.

 

As you suggest, though, I can cope either way; even throwing an error and picking it up with IFERROR.

I guess 4th position is more suitable for people who consider INDEX/MATCH as too complex compare to VLOOKUP, other words for major part of users. For the rest 4th or 6th position most probably is not a big deal, each has pros and cons.

Microsoft

Thanks Folks, it really was tricky. The expert user in me likes the symmetry of having the [if_not_found] at the end too but one of the primary goal was to release a lookup function that could be embraced by Excel's broad userbase of varying skill. This meant leaning towards simplicity for common cases, while offering more advanced variations via later arguments. Us advanced users are better able to understand an empty 4th argument for advanced variations than beginners needing to understand an empty 4th and 5th argument simply to get to [if_not_found].

Frequent Contributor

Thanks @Joe McDaid . I was really questioning why it wasn't the 5th parameter vs the 4th or 6th. The Search_Mode is really obscure and will rarely be used. I cannot even come up with a realistic scenario for it, though I am sure there are some.

 

My concern is working with users that want a "near match" as in the tax table example, but don't want to have an If_Not_Found option. Having the ,, in the function makes sense to me, but not to the average VLOOKUP user. They will think they have to populate that 4th argument to get to the 5th.

@Ed Hansberry the question is what’s more common a need for the average user?  Approximate match or handling #N/A ?

 

My experience from working at many different clients and training hundreds over the last 20 years is that it’s the #N/A

Frequent Contributor

I agree @Wyn Hopkins , but if you are going to handle the #N/A, you are also going to know you are wanting an exact match, or a range match. My concern is that people will want to use a range match, but unknowingly eliminate error detection because they will fill in the If_Not_Found, thinking they have to fill it in.

Valued Contributor

@Ed Hansberry 


The Search_Mode is really obscure and will rarely be used. I cannot even come up with a realistic scenario for it, though I am sure there are some.
= IF( [@Status]="Clock Out",
    [@[Event time]] -
        XLOOKUP( [@[Access card number]],
            [Access card number] * ( [Event time] < [@[Event time]] ),
            [Event time],
            [@[Event time]],
            0,
            -1 ),
    "" )

 The above might provide an example.  It is taken from a table of clock-in / clock out times.   The objective is to calculate a shift length each time the employee clocks out.  The search is for a matching access card number with earlier timestamp. By searching from the end, the most recent event is returned.

 

It is not such a tragedy if the user feels obliged to provide a response to the 'if not found' parameter.  Here 'clock out' without at prior 'clock in' would cause an error but I chose to deal with it at source by returning the current event clock time in order to give a zero duration for the shift.  The alternative is to raise a deliberate error and then trap it with IFERROR but that is neither obviously simpler nor better.

Microsoft

One of the primary uses for the larger/smaller match is tax tables. It can be can be used to determine the marginal tax rate a tax payer falls into.

 

Fun fact: The tax use case was why the original LOOKUP was devised. It was developed to assist with Bob Frankston's 1979 tax return way back in the early days of spreadsheeting. (source)

Super Contributor

XLOOKUP is nice. Let's not overlook the power of the functions FILTER and SEQUENCE.  FILTER can perform most any lookup XLOOKUP typically does and can accept multiple criteria without concatenating the lookup value and/or lookup arrays.  Furthermore, FILTER can spill multiple results vertically or horizontally.

Frequent Contributor

FILTER doesn't work in tables, so it is a non-starter for me unless it is a one-off formula. I'd be curious to see workbook speed with 100,000 FILTER() functions vs 100,000 XLOOKUP functions though.

Hey @Ed Hansberry   I did a quick test a month or so back

 

Data Set 20,000 rows

 

20,000 FILTER()  v 20,000 XLOOKUP()

 

XLOOKUP = INSTANT

 

FILTER = 60 Seconds +

Frequent Contributor

XLOOKUP is smoking fast.

Valued Contributor

@Patrick2788 

Logically FILTER could replace any lookup function or xIFS (SUMIFS etc.) function.  That is not to say it should, that is a case by case decision in which both performance and flexibility (e.g. speed versus multiple return values) are important considerations.

@Ed Hansberry 

FILTER works within a table provided it is set up to return only a single value, maybe by using @FILTER.  More useful, is to use such array formulas outside any table; they will still benefit from having structured references as parameters and will adjust dynamically to match the Tables they reference.

Valued Contributor

@Ed Hansberry 

You may feel vindicated to know that I managed to fall into the 'parameter 4' trap.  I was performing a 2D interpolation of gas density versus temperature (horizontal axis) and gauge pressure (vertical axis).  Given a pressure, I need to return the next higher and lower values from the table in order to be able to interpolate the density.  The formulas I needed were

= XLOOKUP( GaugePressure,  PressureTbl, PressureTbl, , {-1;1} )

= XLOOKUP( Temperature,  TemperatureTbl, TemperatureTbl, , {-1,1} )

but, sure enough, I forgot and put the match mode in the wrong place.

 

I had also encountered the problem of XLOOKUP not returning 'arrays of arrays' that you mentioned earlier.  If I required a single 2D lookup, I could do it by range intersection using

= XLOOKUP( Temperature,  TemperatureTbl, AirDensity, , -1 )   XLOOKUP( GaugePressure,  PressureTbl, AirDensity, , -1 )

but XLOOKUP will not return the pairs of rows or columns that I need.  Consequently, I reverted to INDEX/XMATCH to produce a 2x2 array of results

= INDEX( AirDensity, XMATCH( GaugePressure,  PressureTbl, {-1;1} ), XMATCH( Temperature,  TemperatureTbl, {-1,1} ) )

In the case of XMATCH, the match mode parameter is where I have come to expect it since there is no 'if not found' parameter.  That set me up perfectly to fall into the XLOOKUP trap.

 

Slightly rueful but @Wyn Hopkins could alway argue that, since I dug myself out of the hole, I do not require help in the form of any alternative parameter order.

Regular Visitor
Great
Regular Visitor

It was common knowledge that INDEX and MATCH performed better than VLOOKUP on larger datasets, but will XLOOKUP bring performance benefits with it too? 

@statto , in latest versions of Excel it's not obvious what have better performance, INDEX/MATCH or VLOOKUP. Perhaps depends on concrete situation. As for the XLOOKUP it's in beta phase now, I hope on the final phase it'll be quite good from the performance point of view.

Regular Visitor

Still the formula not available in my excel. I need to check the performance with my data

 

We are all waiting with excitement

Frequent Contributor

XLOOKUP just showed up for some users in our tenant which is on the Monthly Targeted cycle. Excel/Office version is Version 1912 b12325.20172

Microsoft

@Ed Hansberry XLOOKUP was just turned on for all users in the Monthly Targeted channels  

@Ed Hansberry  - yes, deployment was started about a month ago on this channel. Hope it'll be no issues and will see it on Monthly channel in relatively short while.

Frequent Contributor

I'll be testing it today. We have a workbook with a few thousand rows and I just replaced all VLOOKUPS with 2 values in an array to find and all INDEX/MATCH statements with XLOOKUP. We are going to have 5 people keying  in inventory tickets and let's see how it goes. 

Frequent Contributor

Our test seemed to go well. 5 people entering data. No one's Excel got kicked out of the workbook, and everyone said it was much faster in data entry than last year. Not sure how much of that is related to XLOOKUP vs the new Dynamic Array engine, as the old lookup method required a CSE array to work. This one doesn't. That is really XLOOKUP replacing the CSE VLOOKUP. Still has the @ symbol in front of every formula so Excel thinks this is an implicit intersection issue, and who am I to argue on that?

Frequent Contributor

@Joe McDaid since you are on the "let's push this out" team, do you know why in Insider version 2001 build 12410.20000 Power Query seems to have reverted to 2018 or earlier? M Intellisense is gone, as is column quality, profile, and distribution. But XLOOKUP, Dynamic Arrays, and Workbook Statistics are there. So Excel seems up to date, but Power Query took a major leap backwards.

These features still exist in Monthly Targeted 1912 b12325.20172, so just missing from Insider Fast....

Valued Contributor

@Ed Hansberry 

I am struggling to follow what the original array-entered VLOOKUP is doing.  Which input parameter is the array; does the formula produce multi-cell output or a single value?

Version history
Last update:
‎Mar 31 2020 01:39 PM
Updated by: