Announcing XLOOKUP
Published Aug 28 2019 09:00 AM 817K Views
Microsoft

March 31st Update
XLOOKUP is now available to all Office 365 Subscribers in the Monthly Channel and 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

218 Comments
Microsoft

@Ed Hansberry , thanks for reporting the issue in Power Query. We identified the bug and fixed it. You should see the M Intellisense and Data Profiling back soon.

Steel Contributor

Thanks @Guy Hunkin !

@Ed Hansberry , intellisense and profiling are appeared in my Excel few minutes ago, same build as before, just re-started the Excel.

Steel Contributor

Yup @Sergei Baklan - just got back to my desk and opened Excel. All is back to normal in Power Query.

Steel Contributor

Sorry @Peter Bartholomew - I always referred to it as a VLOOKUP which is incorrect. It was using an INDEX/MATCH as a CSE to have two critera. It was from this page that uses a function like this. 

 

=INDEX(C4:H1159,MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0),6)

When explaining to my co-workers, I just said it was essentially a VLOOKUP with multiple criteria, and referred to it here as the same. Still much slower than the XLOOKUP() is with multiple criteria.

@Ed Hansberry , as a bonus - if you shift on White theme for the Office, it'll be dark theme for Power Query. Better if we could select the latest, but now it works as it works.

@Ed Hansberry , to avoid CSE you may use it as

=INDEX(C4:H1159,MATCH(1,INDEX((C4:C1159=A2)*(D4:D1159=B2),0),0),6)
Steel Contributor

Well, CSE doesn't work anymore anyway. It just gets the @ for the never-launched SINGLE() function put in there (unless you use CSE to limit the spill range). 

But XLOOKUP works better, and is supported on all of our clients now, so INDEX/MATCH is dead to me. DEAD! :xd:

Copper Contributor
Hello, i am new, how do a create a new post? I have a doubt and I cannot find an answer

@agustirrr ,  go here https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral and click the Start a new Conversation button

Copper Contributor

How can i start use Xlookup& Xmatch  in my excel. 

@shoaib_mkn , if you are Office365 subscriber shift on Insider channel as here https://insider.office.com/en-gb/

Steel Contributor

@Sergei Baklan you only need Monthly Targeted now. It has been fully pushed out to that channel as well.

@Ed Hansberry , Monthly Targeted is also considered as insiders channel (former Insiders Slow). Most advanced production channel is Monthly one.

Copper Contributor

So when might XLOOKUP be generally available to the O365 subscribed masses?

@positiveguy1960 , what do you mean under "O365 subscribed masses"? Anyway, I don't think anyone could articulate the timeline now, if to speak about semi-annual channel my expectation is not earlier than next Fall.

Steel Contributor

@Sergei Baklan I understand, but if anyone wants it and doesn't want to be bleeding edge, they should get Monthly Targeted, not Insider.

@Ed Hansberry , yes, agree. Except terminology, Monthly Targeted is also Insider.

Steel Contributor

Not to get too pedantic @Sergei Baklan but I don't think it is. It used to be Fast Insider, Slow Insider, Monthly, and Deferred. Now it is Insider, Monthly Targeted, Monthly, and Deferred (actually Semi-Annual Targeted and Semi-Annual now). There is just one class of Insider now. If you go to the Office 365 Deployment Configuration tool, it only shows 4 channels - Monthly Targeted, Monthly, Semi-Annual Targeted, and Semi-Annual. You cannot use that tool to generate any Insider deployment config file.

 

So the Targeted channels seem to be a slightly faster version of the non-targeted. I don't think Monthly Targeted qualifies as insider anymore than Semi-Annual Targeted does.

@Ed Hansberry , on the other hand if you go on insider.office.com you will see that insiders channels mentioned here are

image.png

In blogs and other places on this site Monthly Targeted is also mentioned as insiders channel, e.g.

Now, Windows Office Insiders in Monthly Channel (Targeted) can access their release notes... (Monthly Channel (Targeted) Release Notes In App  )

I guess Semi-Annual Targeted is also considered as kind of insiders channel in opposite to production ones (Monthly and Semi-Annual).

Steel Contributor

Perfect @Sergei Baklan. In renaming, Microsoft has perfectly muddled what used to be crystal clear. :unamused:

@Ed Hansberry , transparency and cleanse are bit better than it was before, but still there are some issues.

All the best for the coming year and happy holidays!

Silver Contributor

@JoeMcDaid 

Early on within this discussion/announcement you stated

"Your formulas are generating 'Arrays of Ranges'. 'Arrays of Arrays' and 'Arrays of Ranges' are often truncated by the calc engine.

It's always been like this, it's just easier to hit these days."

 

May I respectfully suggest that such a position will not be good enough in future.

 

The improvement offered by dynamic arrays is amazing but it should only be the start of raising Excel to the level whereby it becomes a serious professional solution development platform as well as simply a manifestation of end-use-computing.  My objective when developing a solution is not merely to use DA where necessary, it is to create a solution that is dynamic in its entirety.  As it stands, dynamic array functionality will work well on a single row or column of variable length, but Excel is based upon a two dimensional grid and the assumption should be that any such calculation may be repeated across (or down) the sheet to give an array of results.  Simply copying the formula is not good enough since the number of instances will not respond dynamically.

 

An example is provided by a workbook I wrote to address a challenge set by @Oz du Soleil 

PooledPresents.xlsx

The number of parties contributing to a shared present buying exercise could easily change so I wanted the table of contributions made towards the presents to be dynamic in both directions.  What I wanted to do is use XLOOKUP to generate the table of contributions from the heading row of people's names.  What I had to do instead, was look up the final column and use range composition ":" to build the dynamic 2D range.

I then wanted to apply SUMIFS to each of an array of columns (as a single array formula); again, not possible as it stands. 

 

What I did instead was generate a matrix of filters from the criteria an use MMULT to sum the filtered values for each column, giving a matrix of results.  That may be OK for me as a professionally-qualified mathematician but it is not what I would wish to offer the Excel community!

 

I can see that you may well have enough demands upon your time rolling out the existing functionality (which already comes as a huge relief to me as it takes me past the user-hostile environment of CSE arrays) but I believe some planning for full 2D functionality is needed now, if only to avoid the creation of future 'legacy/compatibility' issues that would rate as self-inflicted damage.

 

p.s. The link is intended to be to onedrive location but I could consider other means of sharing if required.

Steel Contributor

100% Agree @Peter Bartholomew

"It's always been like this, it's just easier to hit these days." doesn't work IMHO. Dynamic Arrays haven't always existed, so they have surfaced a problem rarely hit, but will be hit more often and will frustrate and confuse users trying to make the most of the DA functionality. It seems wholly arbitrary to the average or above average Excel user.

Microsoft

@Peter Bartholomew @Ed Hansberry I was not saying that this was desirable behavior, rather I was observing that this is not technically a Dynamic Array bug as it is consistent with Excel's long-standing array calc. Going forward we can expand and improve array calc as a separate effort. If you'd like, you could submit this as a UserVoice request; I expect it will garner quite a few votes and this will help us prioritize the work.

 

Steel Contributor

Thanks @JoeMcDaid - appreciate the response. @Peter Bartholomew - you have a much MUCH deeper knowledge of the issue if you want to create the request and post the link back here. Or I can if you'd rather not.

Silver Contributor

@Ed Hansberry 

I will do that.  If there is no action from me after a couple of weeks by all means step in and take the initiative.  What I plan to do first is to alert some contacts that I believe will have informed opinions and be interested in this discussion (though strictly speaking it is an announcement rather than a discussion), in order to see whether there are fresh ideas to incorporate.  At present the pool of potential 'up-voters' (if that term can be used as a compound noun) on UserVoice will be very small, so the plea here is one of 'please do not do anything that may stymie future development'.  From @JoeMcDaid's response we appear to be in safe hands. 

 

Something I didn't dare suggest for the longer term is to build upon pivot table concepts to create dynamic representations of multidimensional arrays, which can then be used in formulas.  More mundanely, I would love to see array formulas replace the automatic user response of 'copying formulas down' which, in the main, is just a cheap and cheerful way of expressing an array relationship.

@Peter Bartholomew , I'll try to find more voters if you formulate an idea on uservoice. In any case I don't think it'll be a lot of them. But I don't think number of voters is very critical. At the same time, on the one hand some ideas with very few votes are implemented quite fast. I guess if it costs practically nothing or it was already in plans. On the other hand ideas with hundreds or even thousands of votes could be on hold for years.

 

And it helps if to sale the idea properly. I'm not  a salesman, perhaps it shall be shall be searchable and not abstract subject, quite short and understandable for majority of users sample. It could be adjusted in comments, however my impression very few read them.

Good luck.

Steel Contributor
@Peter Bartholomew I'd be one to support a uservoice request for handling of nested arrays in formulas.
 
Referring to the PooledPresents.xlsx sample file, and without fully grasping the logic, the following appears to return a 2d array:
=SUMIFS(INDIRECT("PresentContributions["&Contributor&"]"),PresentContributions[Buyer],Payee)
 
as does substituting the INDIRECT portion of the formula with something along the lines of:
OFFSET(PresentContributions,,{3,4,5,6},,1)
 
Unfortunately one is not able to substitute a non-volatile equivalent such as:
INDEX(PresentContributions,,{4,5,6,7})
 
A request for a complementary XINDEX function that allows for multiple ranges might be a possible compromise for covering such use cases more efficiently and transparently.

@lori_m My suggested function for this is SLICES(Range_Array,[SliceRows],[SliceColumns])

 

SliceRows (and similarly SliceColumns) can be zero, or an array of numbers (can be non-contigous) or an array of labels, or one or more ranges intersecting the first row of Range_array (multiple disjoint ranges must be enclosed in () so (a1:B1,D1) results in 1 ,2 and 4).

If the result of SLICES is a contiguous area resolvable to a range, it returns a range, otherwise an array.

 

SLICES is one of a large number of testable dynamic array functions you can download and try from  http://www.decisionmodels.com/fxlv4_speedtools_beta1.htm

I forgot to say that I think its useful with functions like SLICES to be able to specify negative numbers that work from the bottom up or right to left so that you can for instance easily get the last row or column or cell of a dynamically expanding array.

Silver Contributor

@Charles Williams 

You were at the top of my list of those I was going to contact by email before committing a post to UserVoice.

I think the management of arrays of arrays (or arrays of ranges) is very closely related to your SUMROWS and SUMCOLS functions.  I think such functions cover most of the requirement and offer an easy win.  Despite that, I think the arrays of arrays issue is one that is worth getting right because potentially it applies to just about any function.  Here I wanted to return an array of ranges using XLOOKUP and then conditionally aggregate each range with SUMIFS to obtain a row of totals.  I would appreciate any further thoughts you may have.  Meanwhile I will try your SLICES function; I assumed it returns arrays rather than references but some experimentation is called for.

 

@lori_m 

You seem to have a flair for achieving the impossible ! :cool:

I don't know what made you think of INDIRECT, but it works perfectly to give an array of ranges.

Following your idea, I also tried

= SUMIFS( EVALUATE( "Table1[" & Table1[#Headers] & "]" ), Buyer, Payee )

which works as a named formula.

 

@Sergei BaklanThank you for the offered support.  I will try to put something together over the next few days.

Steel Contributor

@Charles WilliamsThose functions look very useful, I see my uservoice request for a reshaping array function is already fulfilled! I should clarify that the suggestion for an INDEX-like function was supposed to return an array of ranges type of result that could be inserted into other functions like SUMIFS


@Peter BartholomewIt wasn't really a huge mental leap, and I hadn't thought of EVALUATE which removes the volatility - one might just need to ensure there is a table dependency somewhere in the formula to take care of recalculation

 @lori_m @Peter Bartholomew Excel does in theory support multi-area ranges and a (limited) union operator, but in practice almost all native Functions do not support them. (The alternative form of INDEX being one exception). So SLICES does not output multi-area ranges because nothing much could consume the result.

 

SLICES does return a range if the the result is a single-area contiguous range, but for non-contiguous results it stacks the areas into a contiguous 2d array and returns that. With the current calc engine and formula syntax thats probably about as far as you can go unless anyone has a better idea!

 

SLICES and some of the other XLL functions support multi-area ranges as input for determining the rows and columns to use.

Steel Contributor

@Charles WilliamsI'm not sure that this proposal would even be possible to implement as an add-in function.

 

My understanding is that multi-area ranges are processed as a single entity reference (Range1,Range2,...) which as you say don't work in many functions.

 

OTOH the native OFFSET and INDIRECT functions can 'lift' range returns to arrays {Range1,Range2,...} which some functions such as SUMIFS can process.

 

This description is probably inaccurate however and MS dev team would need to confirm details.

Steel Contributor

@Peter Bartholomew
Further testing of the EVALUATE named formula showed up some issues when inserted inside functions for me. A request for an alternative along the lines of XINDEX(Table1[#All],,Table1[#Headers]) could be a possibility? For context the equivalent Lotus-123 function accepted text labels as indices as well as multi-sheet references and has several related requests (
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/35913220...)

Silver Contributor

@lori_m 

I am not entirely surprised that there were issues with EVALUATE.  My testing was limited to one trial that worked, both on entering the formula and in response to changes within the referenced data.  On the other had, attempting to output the named formula to the grid gave a #VALUE! with the message "A value used in the formula is of the wrong data type".  The SUMIFS accepted it nonetheless which is somewhat odd behaviour.  The use of CSE solved the error but precludes all dynamic behaviour.

 

I like the UserVoice proposal well enough if one's focus is upon tabular data in which field names and primary keys could move position.  A couple of intersecting XLOOKUPS would do the same job but a key word version of INDEX would be more concise.

 

I am more focused on array functionality in which data is properly accessed by index, so I am willing to accept

= INDEX( PresentContributions, 9, {4,5,6,7} )

and

= INDEX( PresentContributions, , 4 )

but would really want 

= INDEX( PresentContributions, , {4,5,6,7} )

to return an array of ranges.

Interestingly 

= SUMIFS( INDEX( PresentContributions, , {4,5,6,7} ), PresentContributions[Buyer], "Jay" )

works as a CSE formula but not DA.

 

Mind you, as a mathematician, I never understood why the misnamed INDEX function was ever needed

= PresentContributions( 9, {4,5,6,7} )

would be far closer to standard programming notation.  I also note that it returns a #REF! error which suggests some processing has taken place.  Of course, when the original spreadsheet architect states "You could do it the programmers' way but that would be tedious", what should one expect? 

Silver Contributor

Update.  I am getting responses to my request for feedback, both to an email I sent and a number of LinkedIn messages.  The responses are 100% supportive but with slight differences of emphasis.  Some lean toward addressing the underlying 'array of ranges' challenge but one or two focus more specifically on XLOOKUP's ability (or otherwise) to return entire records for an array of lookup values, e.g. 

= XLOOKUP( name, Table1[name], Table1 )

where 'name' represents a range or array of values.  I suspect it is the latter that provides an issue more in keeping with the spirit of UserVoice despite the fact that the 'array of arrays' issue will recur in many contexts.

Silver Contributor

On a separate issue, I have had further thoughts on the search mode parameter and would recommend that the values ±2, indicating binary search ascending or descending, should not be included in the general release version.  It may be that there is a demand to specify search strategy but I very much doubt that to be the case.

 

Not committing the functionality would allow the way forward to having XLOOKUP return 2nd and 3rd instances of matching records.  Thus

= XLOOKUP( "Smith & Sons", Sales[Company Name], Sales, , , -{1;2;3} )

might return the 3 most recent records from the table of sales for a specific customer.

Steel Contributor

@JoeMcDaid why does XMATCH() not have an [if_not_found] setting? Seems it would have been consistent with XLOOKUP. I just had to wrap XMATCH with an IFERROR...

Silver Contributor

I have posted the following

XLOOKUP to return an array of ranges

Currently XLOOKUP can return an array of return values, given an array of search values, OR a range corresponding to a single search value.

Like other functions, it does not return an array of ranges but, rather, truncates the calculation.

This may have been acceptable in the past when the expectation was to copy formulas across to return values that logically form an array.

As dynamic arrays receive increasing use, the expectation will change and the failure of a range to spill will be far more problematic.

 

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/39403732...

Steel Contributor

I like the concept but wouldn't 'arrays of ranges' be double dutch to most users? I still struggle with that concept after many years. You could always link back to this thread if needed.

 

Something a bit punchier could be "XLOOKUP to support dynamic crosstabular reporting" including simple examples of spilled array formulas in one and two dimensions using SUBTOTAL and SUMIFS functions respectively.

Steel Contributor

Voted @Peter Bartholomew - Thanks. I also attached a workbook based on the original discussion that shows what the issue is. Of course, if comments start getting added, my comment will get pushed to the bottom.

 

Hope this gets the attention it deserves!

Silver Contributor

@lori_m 

I suspect you are right in that there is a touch of 'geeks corner' about the request.  The problem may be that my suggestion attempts to straddle two objectives.  The more restricted but understandable is to improve the XLOOKUP function to eliminate what might be seen as an unexpected fail.

[Thanks to @Ed Hansberry for his example clarifying the point on UserVoice and to @Wyn Hopkins for the link to @Mynda Treacy 's new blog post which draws attention to the 'feature'].

 

The question I might ask of @JoeMcDaid though is the background issue of "Is it possible for Excel to handle 'arrays of arrays' and 'arrays of ranges' in a generic way to turn what is now a problem into the basis for new functionality?"  This is far more obscure but the payoff would be when

FUNCTION( array of arrays ) is evaluated, to give an

Array of FUNCTION( arrays ).

 

Given a table (say), that could be the SUM of each column, a conditional SUMIFS, other aggregations such as AND, (or even new functions such as UNIQUE to give a set of validation lists).  Row sums would work in a similar manner but would require an array of rows to be the starting point.  It all can be done now by manually copying the formula after each change to the referenced data but for the result to spill would be far better.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/36151087...

Now, that is geeky! :(

Copper Contributor

Please awnser the questions:

 

when!!??

Steel Contributor

@janremuebpostbe - it is already out in Insider and Monthly Targeted. They just released Dynamic Arrays to Monthly this week, and XLOOKUP as the next thing in development, so it should be coming to Monthly next, but that might be next week or mid-march. There is no published timeline and it will come out when MS feels testing results is such that they push it out to their main customer base. Deferred will likely get it 6 months or so after that.

Brass Contributor

@JoeMcDaid I wish there could be an option to return

--a value other than the first value, if multiple values are available for the Lookup_Value

--all the values in the same cell (with some delimiter) or array of rows, if multiple values are available for the Lookup_Value

Presently we can return only the First value found from the Return_Array

@KanwalNo1 , I guess you may use FILTER() for that

Brass Contributor

@Sergei Baklan Thanks Sergei, Can you elaborate a Bit !

What I am trying to accomplish is to return a 2nd or 3rd or nth instance of the Value from the Return_Array

@KanwalNo1 , I mean something like this

image.png

Co-Authors
Version history
Last update:
‎Jun 25 2021 02:19 PM
Updated by: