Announcing XLOOKUP

[Joe McDaid]

October 29th Update
While preparing XLOOKUP for general release, we have been listening to the incredible feedback from our Insiders. A leading suggestion was that XLOOKUP could be made even better by adding an [if_not_found] argument. We’ve heard you loud and clear and have gone ahead and added it!

We initially rolled out [if_not_found] as the 6th argument to XLOOKUP, but will promote it to the 4th argument as it will see more usage than [match_mode] and [search_mode]. This change will alter the behavior of any pre-existing XLOOKUPs that use four or more arguments. If you have any XLOOKUPs that do, you will need to revise them once you get the update to ensure they continue to calculate as you intended.

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 34 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:

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,[match_mode],[search_mode])

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

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:

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,[match_mode],[search_mode])

Learn More

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

• XLOOKUP help
• XMATCH help

Availability Notes

XLOOKUP and XMATCH are available for users signed up for the Office 365 Insiders Program starting today and will continue rolling out to Insiders over the next few weeks. Gradual roll outs allow us to gather feedback and ensure feature quality.

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

[Detlef Lewin]

Great. I suppose with XMATCH() it would be easier to program a universal cross tabular lookup with all possible variations. Although I don't mind getting an extra function CTLOOKUP().

[Sergei Baklan]

Great!

[Soumalya Biswas]

wow !!!!

[Sergei Baklan]

And here are more samples from Bill (MrExcel), he is fast, as usual -  The VLOOKUP Slayer: XLOOKUP Debuts Excel

[Jude Barak]

Thanks! very good news. Will it work "backwords" too? instead of index & match?

[Ed Hansberry]

Excellent. I can stop using INDEX/MATCH. XLOOKUP handles it all!

Hoping these have a fairly short trial in Insider builds before hitting Monthly Targeted.

[Katharina_Schwarzer]

What a great enhancement.
This will be much easier to be taught to beginners - I'm SO looking forward to be able to use and teach this new function! Thank you very much for considering the suggestion.

[Ingeborg Hawighorst]

@Jude Barak Yes. That's one of its benefits.

[Peter Bartholomew]

Hi@Joe McDaid 

May I ask a question?

One thing I have experimented with recently is 

= LOOKUP( lookupValues, SORT(array) )

with the objective of getting a large number of bisection lookups of the back of a single SORT.

I could do the same thing with XLOOKUP

= XLOOKUP( lookupValues, SORT(list), SORTBY(result, list), 0, 2 )

it works well, but is there any point?  I seem to remember statements to the effect that exact lookups are now as efficient as those performed by bisection searches on sorted data -- or have I got that wrong?

[Joe McDaid]

@Peter Bartholomew The calc engine will optimize the lookups for you internally, no need to presort. I actually suspect you will find that it's slower to sort inside your function like that.

Personally, I see very little value in using 2 or -2 for the last argument -- its there so that XLOOKUP can be considered a superset of VLOOKUP because its intended to be a successor. That way we can be sure that XLOOKUP can be used in place of VLOOKUP even for obscure use cases. We want users to be able to make a clean break.

[Wyn Hopkins]

Simply awesome

[Joe McDaid]

@Wyn Hopkins Thank you for driving the community request via Uservoice! Your submission and the community votes it gathered helped us prioritize this work.

[RobDonaldson]

Well done Microsoft Excel team, and well done Wyn Hopkins.

[M_Cherian]

This is a massive leap for Excel Users.

As a Trainer and Instructor, I know the effort and patience behind letting people know, how to replace the column index no: with column() or Match().

In a simple and straightforward note, Xlookup, technically, has only Pro's; the only Con I can think of is, it beats the joy, sense of achievement and glory one feels, when they master VLOOKUP with Column() and Match() combination.

Absolutely brilliant work, Excel Team!!

Worth the wait for millions of users.!!

[Washivale]

Awesome!! Looking forward to see it in action soon.

Thank You Team Excel!!

[Avi_v]

Great news! ETA for "regular people"?

[Akio Takahashi]

Simple & Great !

[Roger Govier]

Two fabulous new functions Joe which we have been seeking for some time. A minor change in the learning curve for existing users, but so much easier for all future users who will never get caught out by Vlookup again.

[Peter Bartholomew]

I forgot to offer my congratulations to the development team in my earlier post.  For me, traditional Excel appears plagued by 'cheap and cheerful' techniques that provide partial solutions but are constructed so as 'not to place demands upon the end-user'.  All of the new dynamic array functions seem to have brought with them a noticeable greater degree of rigour.  More thought appears to have gone into the planning, so all the use cases that one might reasonably expect seem to be catered for.  Ultimately, this reduces the need for the 'tips and tricks' that provide workarounds or guidance where there is overlapping functionality.  Great stuff!

[akmych]

a question -- will be this new function case-sensitive? Right now VLOOKUP is not case-sensitive, so search for "TEXT" or "Text" or "teXT" will return the same first string, what about XLOOKUP?

[Ben_Norris]

Interesting stuff - anything to get people away from using VLOOKUP is a good thing.

Would be interested to know if Charles Williams has done any speed testing between XLOOKUP, VLOOKUP and INDEX/MATCH

[idannet]

That's great news. A new era. No more match and index.

What will happen if the spreadsheet is opened on an old Excel? 

[jlewis40]

When will this function be available? I want to give it a try. Also, any chance of resurrecting Clippit, Merlin, Rocky, F1, Links among others (MS Office Assistant)?

[OpenAthensAndy]

Looks cool - which version should it show up in please?

[James McPherson]

My colleagues love a Vlookup but if they don't use it for a while they can sometimes struggle to understand how to use it, I have a feeling that this will change that and Excel will become even more loved in the office!

I'm looking forward to the advanced searches myself 

[sifar786]

This will make life easier! Too many Pros! Only one Con i can think of right now - a case-sensitive lookup or match, is no possible! Also, it would be good to do away with FIND & SEARCH functions and incorporate their case-sensitivity into XMatch or XLookup.

[Ed Hansberry]

@akmych it is not case sensitive. Even with the Exact Match set, it won't work. You would still have to use the EXACT() function in conjunction with this to get it to be a case senstitive match. XMATCH() is the same way, case insensitive.

[Ed Hansberry]

@idannet When you open a workbook in an older version, the previously calculated formulas keep their values. But if you change any cells/tables that the new functions depend on, the XMATCH/XLOOKUP functions return #NAME? errors and show

=_xlfn.XLOOKUP()

for the function name. As long as you don't edit that, the new version will fix that when you open it in that version, but it will error out again in the old version. Pretty much like any other new function not available in older versions, like CONCAT, UNIQUE, etc.

[netboyz]

I don't have access yet to XLOOKUP so can't check this myself: Does XLOOKUP work in Excel Structured Tables?

Syntax using StructRefs not cell addresses, like:

=XLOOKUP(rowNameString, srcTableReference, XLOOKUP(columnNameString, srcTableName[#Headers], srcTableReference))

Also, would like to see XLOOKUP and XMATCH included in Application.WorksheetFunction so they can be used from VBA.

[Claude_B]

Great! tell me @Joe McDaid : What's gone be the French function? RECHERCHEX is not working yet.

[akmych]

@Ed Hansberry can you please elaborate a little about how I can use VLOOKUP in conjunction with EXACT() to get a case-sensitive search?

[Ed Hansberry]

@netboyz Yes, mostly. If your source data is in a Table, it works great. If your XLOOKUP() is in an Excel Table, then maybe. It is returning one value, it works fine. If it returns an array, it probably won't.

=XLOOKUP(Table3[@Item],Table1[Item],Table1[[Amount]:[Amount2]])

That works fine in an Excel cell. It returns a two column array that spills into the next cell. (spilling introduced with dynamic arrays.)

It does not work in a Table though. Even if your table has two columns to handle the data, the first column just gives the #SPILL! error and the second column is empty.

[Ed Hansberry]

@akmych See this article for a number of different ways to get a case sensitive search done.

[Kevin Hankins]

There needs to be an online tool where we can input our version of Excel and get back WHEN WHAT will become available.  Without this, our hopes are dim.  We cannot check every day!!

[Kevin Hankins]

I have used vlookup for most of these years.  In the past few days (Excel 2016, 2019 Enterprise) vlookup(exact) has failed twice, returning totally wrong results.  Might this be linked to updates in Excel?

[Gregor_Y]

This kinda feels too little too late to me. I wrote it as a UDF five years ago and already includes most of what everyone's asking for here ie case sensitivity and a few things they aren't like Nth match. Splitting the look-in range and the return value range into two arguments is a bit of an interesting approach, and I am wondering if it'll work when you throw two or higher dimensional references at it or maybe even row to column pivoting. However the real questions are where are the new features that'll keep Excel alive and kicking in the data space for the next generations to come? REGEXLOOKUP(), tree, orbital, or hex grid data tabs, relational joins, histograms, non-linear regression lines, high-dim clustering analysis, CRYPTORAND(), density plots, B2B data pipelining, ... the list goes on. The whole selling point of Excel is that it packages these tools into an easyish to use and experiment with environment where your average person can poke around with it in an interactive way; however when other tools like Python, R, and the various web based visualization suites are easier to use then Excel, it loses some of its curb-appeal.

[Peter Bartholomew]

@Ed Hansberry 

I had a play with your discussion problem.  As you specified, I used a table for the source data.  I also used a table for the lookup values but I placed the formula in a cell near the criteria table.

= XLOOKUP( @Criteria[Item], Source[Item], Source[[Amount1]:[Amount2]] )

spilt horizontally as required but, being external to any table, the formula does not fill down automatically.

I then tried to use the Item column from the criteria table in its entirety as an array

= XLOOKUP( Criteria[Item], Source[Item], Source[[Amount1]:[Amount2]] )

The result array then spills the full length of the column but truncates to a single column.  Since I felt that it is more important that the array behaviour down the column is dynamic, I settled for that and added a further column

= XLOOKUP( Criteria[Item], Source[Item], Source[Amount2] )

to complete the result range.

The final strategy was to revert to INDEX/MATCH and try the formula

= INDEX( Source[[Amount1]:[Amount2]], XMATCH(Criteria[Item], Source[Item]), SEQUENCE(1,2) )

That required only the one formula to spill over both rows and columns, which was my objective, though not the formula I had originally planned to use.

[netboyz]

@Ed Hansberry  @Peter Bartholomew  I'm still thinking about this, tho.

For my applications, looking up a value from a source Structured Table for use in a destination Structured table, will want to return a single value (or single sum of values), into the field in a row in the Destination Table containing the XLOOKUP expression; replicating this operation in each row of the destination Table.

It's helpful if the return column specification is programmable by data in a field in each destination table row initiating the lookup, so that the return column name doesn't have to be hard-coded into the expression, ie so I can get a 2D lookup. I'm using INDEX/MATCH/MATCH now to get exactly those 2D lookups among Structured Tables, and that provides linking that's nicely dependent on data, and independent of its location. Looking at the announcement of XLOOKUP, I'm stressing on how to specify the return_column in XLOOKUP, finding it from the Destination Table[#Headers] range.

I'm basically checking whether Microsoft targeted XLOOKUP toward use of INDEX/MATCH/MATCH with Structured Tables and if so what they think the syntax looks like. I don't yet have access to XLOOKUP, so can't check it myself, but am interested in what others may find. My description above is what I think it takes to get an equivalent to INDEX/MATCH/MATCH with Structured Tables.

(By the way, I credit Bill Jelen with the 2D XLOOKUP syntax in my example; he stated it using cell addresses, and I'm trying to understand if it targets okay to Structured Tables using Structured References and #Headers, so I can get location independence when linking, which is nicely powerful.)

[Sergei Baklan]

@netboys, it could be like =XLOOKUP([@Item],Source[Item],XLOOKUP([@ColumnName],Source[[#Headers],[Amount1]:[Amount2]],Source[[#Data],[Amount1]:[Amount2]])) Sorry, @mention doesn't work for me at the moment

[Sergei Baklan]

@netboyz , now editor appeared for my commenting, repeat the formula

=XLOOKUP([@Item],Source[Item],XLOOKUP([@ColumnName],Source[[#Headers],[Amount1]:[Amount2]],Source[[#Data],[Amount1]:[Amount2]]))

[Peter Bartholomew]

@Sergei Baklan 

I loved the way in which you selected the desired field for this bi-directional lookup!

I had tried

INDEX( Source, 0, XMATCH(FieldName, Source[#Headers] ) )

and

INDIRECT( "Source[" & FieldName & "]" )

but your solution has elegance.  I allowed it to search the entire Header row giving the slightly shorter formula

XLOOKUP( [@ColumnName], Source[#Headers], Source[#Data] )

[Sergei Baklan]

@Peter Bartholomew , thank you for the correction, you are absolutely right. Even didn't think about other variants trying to do that with nested XLOOKUP.

[Peter Bartholomew]

Spoilt for choice!

FILTER( Source[#Data], Source[#Headers]=Criteria[@ColumnName] )

also works.  Better put that aside since this is an XLOOKUP discussion and I like Sergei's solution better anyway.

[Ed Hansberry]

This, IMHO, seems to be unexpected behavior @Peter Bartholomew 

= XLOOKUP( Criteria[Item], Source[Item], Source[[Amount1]:[Amount2]] )

The result array then spills the full length of the column but truncates to a single column.  Since I felt that it is more important that the array behaviour down the column is dynamic, I settled for that and added a further column

---

Using a formula where the criteria is not in a table:

=XLOOKUP($E$5:$E$9,Table1[Item],Table1[[Data1]:[Data2]],0)

This spills down, but only returns data in the Data1 column.

=XLOOKUP($E$5,Table1[Item],Table1[[Data1]:[Data2]],0)

This spills right and returns values in the Data1 and Data2 column.

Replacing my hard-coded criteria in E5 above with the following:

=UNIQUE(Table1[Item])

allows me to change my XLOOKUP function to:

=XLOOKUP(E5#,Table1[Item],Table1[[Data1]:[Data2]],0)

And it spills down, but will not return the 2nd column.

I cannot see the logic in why it will only spill right if you are referencing a single cell, but will spill down when referencing a range or an array.

Is that by design for some reason @Joe McDaid 

[Ammar Al-Khatib]

super excited for XLOOKUP ... it's a big step forward, especially selecting the column for results not typing the column number. Great Work ....

[Esenam]

This is very convenient. I've been using a combination of Vlookup and Choose to navigate the left-to-right limitation but I think the Xlookup makes it simpler. 

[Joe McDaid]

@Ed Hansberry 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.

[Hamidreza_mohammadzade]

Very nice!

In the way of science you are doing your job perfectly !
I appreciate you and i wish to be a member of your teams !

And i know that its very far to be a member of your teams ...

Kind regards,

Hamidreza 

@Joe McDaid

[Alex2019]

Great "new" feature. I was just in the process of replacing vlookup with index/match. Think i'll wait a bit now

[Sohan_Advani]

What if i want more than one result from different columns

In Vlookup I only have to change column no. in third parameter.