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
Copper Contributor

Excited! ☺️

Silver Contributor

What if i want more than one result from different columns

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

@Sohan_Advani 

 

Depends on the situation.  If the columns are adjacent in the source data you can select the two columns as the return array and the result spills to give the two values.  If they are columns 27 and 63 then, instead of typing 63 when you write the second column formula, you select the corresponding column in the source data (easy with Tables and less error-prone than counting columns).

Copper Contributor

@joe - Great work

Quick question if I use 0 (Exact) and 2 (Binary) as the last two parameters on a sorted search column will this be blazing fast compared to using 0 (Exact) and 1 (Top Linear) 

 

Thanks

Sam

Copper Contributor
Hi All, is there a way to automatically push down the rows with values after executing transpose of the values on top of it?
Copper Contributor

nice

Copper Contributor
Awesome!
Silver Contributor
Awesome! So the first letter of the function name "X" stands for extended or extreme?
Copper Contributor
This is fantastic. We will rust to add XLOOKUP and XMATCH to the Microsoft Excel bootcamps we offer at GraduateTutor.com -https://www.graduatetutor.com/microsoft-excel-tutoring/microsoft-excel-boot-camp/ Great to hear that they are not retiring the Vlookup and Hlooksup. Despite their limitations, I think they will continue to be used over Xlookup because they are far simpler and sufficient for model Excel modeling situations - especially in finance related applications.

@Haytham Amairah , my guess "X" is just a cross meaning it works in any direction

@Senith , I don't see that XLOOKUP in it basis form is more complex than V(H)LOOKUP

Copper Contributor

@JoeMcDaid Wonderful stuff, thank you!

 

It seems the parameter interpretation for match_mode in XMATCH are inverted from those for MATCH. So a match_mode of -1 in XMATCH means exact or next smaller, while the same match_type of -1 for MATCH means exact or next larger. 

Take the XMATCH help Example 2: The formula shown as =XMATCH(F2, C3:C9, 1) will give the same result as =MATCH(F2, C3:C9, -1).

Similarly in XMATCH help Example 4, the formula =XMATCH(4.5,{5,4,3,2,1},1) returns the same as =MATCH(4.5,{5,4,3,2,1},-1).

 

Why not make this parameter interpretation consistent?

 

This might be an intentional change, since XMATCH is a different function and doesn't require the array to be sorted etc. But I found the wording in the help saying "next smallest item" confusing, especially when comparing with the much better MATCH help which says "largest value that is less than or equal". I think this change should be called out somewhere. Otherwise it is tempting to just replace MATCH with XMATCH since it is presumably safer and better (by not requiring the sorted inputs).

 

There are also some minor bugs in the help documentation:

* XMATCH Example 1 - has two mistakes: The search_mode should be 2 for wildcard search, and even then the string "Gra?" is not enough to match "Grape".

* XLOOKUP Example 4 - the numbers in the table and result don't add up, neither for the  Qty * Price = Total columns, nor the XLOOKUP result shown (could be rounding in the lookup result, but looks confusing). 

 

Copper Contributor

Dear team,

 

i have tried this XLOOKUP but that option is not shown in my excel file...how to enable kindly help n this.

 

Regards

Naresh Ratnala

 

@Govert van Drimmelen , XMATCH Exmple 1 is correct. Function uses wildcard search with all modes except 2. In mode 2 wildcard symbols are interpreted as part of the text.

Copper Contributor

@Sohan_Advani i think you could use the offset formula as the target range argument? This way you can specify your lookup range also as your target range and then offset +n columns to the right (or -n to the left) and n could also be an argument pointing to a cell outside this formula etc. just like you probably previously did with Vlookup?

Steel Contributor

@Naresh_ratnala You have to have the Insider version of Office 365 to get this. See this link.

 

Even if you are an insider, you might not have it yet. They roll out some of these things in stages, not to everyone at the same time. 

@Ed Hansberry , exactly. I checked on my PC right after announcement and found these functions. On my laptop I still have nothing. Both with Office Insiders Fast, same subscription plan, same build.

 

Philosophy of deployment is changed a bit for cloud subscriptions. 

@Alex_Ley , with any kind of columns numbering better to use VLOOKUP. With offset return array will be changed if to insert new column in the middle of the table. Same for VLOOKUP scenario with columns numbering, and that's one of the XLOOKUP purposes to avoid such effect.

Copper Contributor

@Sergei Baklan My interpretation of the help documentation for match_mode in both XLOOKUP and XMATCH is that a wildcard is only interpreted for the case with match_type = 2. I am not able to test with the real functions, but the documentation for both XLOOKUP and XMATCH is clear on this, and so the XMATCH Example 1 is not consistent with the documentation.

 

Even the Bill Jelen / MrExcel post calls this out as an advantage of XLOOKUP:

 

Wildcard Support, but Only When You Request It

Every VLOOKUP supported wildcards, making it hard to look up Wal*Mart. By default, XLOOKUP will not use wildcards. If you want wildcard support, you can specify 2 as the Match_Mode.

Copper Contributor

I am using Office 365 in 2 laptops (which is applicable in this subsriptions, I checked on Microsoft site) one of my laptop has the latest updates and the new dynamic arrays too but doesn't have the Xlookup, the other fails to update and shows error code 30088-28, The same laptop had Xlookup and Dynamic arrays 2 days back now I don't have any of those, had a remote screen sharing session with Microsoft Help team but they couldn't help and while reintsalling office I lost what I had, I work primarily on Excel and I need the latest updates that is why I am registered as Office Insider Fast (Insider), had this issue earlier too and couldn't get back the new functions for 4 days, I am not sure if this is the right place to post this toubleshooting query but couldn't get a better place @JoeMcDaid could you please let us know a stable Excel Help portal where we can get this issues resolved, and my first concern is why this keeps on happening, waiting from a resolution from your team as you are the Go To person in Excel.

clipboard_image_0.png

Copper Contributor

Is it safe to assume that XLOOKUP will be a volatile function, like VLOOKUP and HLOOKUP are?

 

That may be one reason to keep using INDEX(MATCH()), which is not volatile.

@phumzani   VLOOKUP is not volatile, and nor will XLOOKUP be

Steel Contributor

@Soumalya Biswas See this thread on the error you are experiencing. It is a known issue and being resolved/discussed there.

Steel Contributor

@Wyn Hopkins - VLOOKUP is sort of volatile, or it was. If you changed any cell in the range you are referring to (the 2nd parameter) VLOOKUP will recalculate, even if the cell you are changing isn't in the first or target column. That may have changed in the last 12 months when they sped VLOOKUP up. I don't know if that was part of the optimization or not.

 

XLOOKUP should only change if data in the lookup/target column changes, just like INDEX/MATCH did. (I speak of Index/match in the past tense, as it is now dead to me. :xd: )

@Ed Hansberry ,  yep so not volatile but will recalculate if there’s a change to the range it references.  Great that XLOOKUP will refer to just the required cells

Copper Contributor

@JoeMcDaid 

Modify the Optional Search mode Parameter to specify Instance number of the Search term in XLOOKUP
Make The [Search mode] parameter to accept values as below
1,2,3 etc to mean 1st instance, 2nd instance etc from the top
-1,-2,-3 to mean 1st instance , 2nd instance etc from bottom

The search algorithm is already specified in the [Match Mode] parameter so there is no need to duplicate it again in [Search mode]

Copper Contributor

What about localized versions? When will it be available in other languages? To be more specific: any idea about when italian users will be able to ghet that and what will be the translation of the formula?

Copper Contributor

For those (like me) who just can't wait for the update, I've made a small add-in called Excel-DNA XFunctions that implements the new XLOOKUP and XMATCH for any (Windows) version of Excel that doesn't have the new functions built in.

It needs a bit more testing, but seems to perfectly match(!) the documentation I've seen so far.

 

Find the project at https://github.com/Excel-DNA/XFunctions

 

 

Copper Contributor

This is fantastic!

Copper Contributor

Is it possible to return multiple non-adjacent column results. For example, in VLOOKUP I could do (LookupValue,LookupArray,{1,5},false) and return a value from columns 1 and 5. XLOOKUP works well with adjacent columns, but I'm not seeing how to reference arbitrary columns. I wish in addition to wildcard, there was also an "all" option, then you could collapse "filter" into this as well.

Silver Contributor

@SpectorZ 

I am not aware of a setting that will allow XLOOKUP to return multiple results from non-adjacent columns.  There are however a number of ways of combining XLOOKUP with other array functions to get the result you hope for.

 

1) You could build an array of output columns using CHOOSE

= XLOOKUP( name, Data[Name], CHOOSE( {1,2}, Data[ID], Data[Value] ) )

2) You could achieve the same starting point by filtering down to pre-select the output columns

= XLOOKUP( name, Data[Name], FILTER( Data, {1,0,0,0,5} ) )

3) You could output an entire record or part thereof and only then filter it

= FILTER( XLOOKUP( name, Data[Name], Data ), {1,0,0,0,5} )

4) Even INDEX could be used to pick out the results you specify

= INDEX( XLOOKUP( name, Data[Name], Data ), {1,5} )

 

My preference would be 1) because I prefer to reference data by name than by index unless it is the position that is important and not the content.  Maybe others have better solutions but this will do me for starters :)

 

Edit:  5) A nested XLOOKUP would also work

= XLOOKUP(  {"ID","Value"}, Data[#Headers], XLOOKUP( name, Data[Name], Data ) )

Copper Contributor

Hi @JoeMcDaid, Excel Team and fellow Community Members!

 

Thank you @JoeMcDaid for this exciting announcement! Is it possible to join a specific Office 365 Insider for Business channel and receive this previewed feature? I'd love to try it (as would other members of our IT Team) if possible! Or is the joining of channel and assignment of this feature more randomized? I guess it doesn't hurt us to join either way because we'd be getting some exciting previews such as this but I was just curious as I am an Excel fangirl and am super excited to try this new feature.

Steel Contributor

@jtrustees Anyone with an Office 365 subscription can be an insider. If you are in a domain, you have to have install rights from your IT department. Other than that, see this site to join. There is a link there to further the documentation for doing it on a business account. The Office Deployment tool is pretty easy to use once you get your configuration file set up.

Copper Contributor

Thats super eXciting :)

Silver Contributor

@xlpbi 

I like the idea.  It would represent a significant extension in functionality but means that the backward compatibility modes ±2 would need to be reserved for this use rather than that currently proposed.  I suppose 0 would still be available for backward compatibility with VLOOKUP, TRUE (Binary search).

 

Copper Contributor

That's awesome, 

 It is a powerful replacement for : VLOOKUP(),HLOOKUP() and INDEX(),MATCH() Functions, not mentioning filtering and sorting data.

 

I can't wait to use it in my spreadsheets.

 

 

 

 

 

 

Copper Contributor

Do you know if it will address the automatic fixup problem with the index for the return value? Right now with HLOOKUP and VLOOKUP if you add a column or row that changes the range start or size, it automatically adjusts the range specified. But if the addition is between the start of the range and the index for the return value, that index is not adjusted.

Copper Contributor

Thanks for the reply @Ed Hansberry! Do you know if as Office 365 Business subscribers we need to join a specific channel (e.g. Semi-Annual, Annual, etc.) to receive this XLOOKUP feature? Or is it more randomized as far as which channel members receive this feature?

Copper Contributor

@Peter Bartholomew 

Thanks, yeah I think the first one makes the most sense to me also. Filter has given me some serious performance issues. I had rather hoped I would be able to do something like {A1:A5,C1:C5}. It works just fine with adjacent columns, so dragging my columns up next to each other might be just as quick. Either way, I'm going to get a lot of mileage out of this one.

Silver Contributor

@jtrustees 

Following the links provided by Ed Hansberry

takes you to https://insider.office.com/en-us/business

"If you're an Office 365 admin, we recommend that you only provide Office Insider builds to a small, select group of users in your organization. These would be users who need to start working with new Office features as soon as they are available, such as application developers or testers, training or help desk staff, or other members of the IT department."

Steel Contributor

@jtrustees You need to be Insider for this XMATCH and XLOOKUP. For Dynamic Arrays, Insider has it, and it is rolling out to Monthly Targeted. Not all Monthly Targeted have it. No Monthly or Deferred have any of this.

Steel Contributor

@andybarnhart XLOOKUP is not impacted by the addition or deletion of any columns or rows unless it is the column or row you are specifically matching or targeting. VLOOKUP and HLOOKUP both break if you insert/delete between the lookup and target columns/rows unless you are using some OFFSET() magic with them.

Copper Contributor

Great News indeed; but what is the main difference between the new proposed XLOOKUP and the current LOOKUP function???

the current LOOKUP function having excatly the same parameters; the main difference that I can clearly see it is the additional search option... 

 

Please advise

Silver Contributor

@MEltrissi 

The main differences are that XLOOKUP works on data irrespective of whether it is sorted or not, whereas LOOKUP requires data to be sorted ascending and then returns a match or the next smaller value.  XLOOKUP defaults to an exact match but can be set to return a value corresponding to the next higher or next lower.  LOOKUP returns a value (or array) from the return range whereas XLOOKUP returns a range reference.

@MEltrissi ,  just to really highlight one of @Peter Bartholomew’s points.  LOOKUP will return a result even if it can’t find an exact match.  This is pretty risky and generally LOOKUP should be avoided.

Copper Contributor

When will xlookup and xmatch become available?

Copper Contributor

Great when i use this formula

Copper Contributor

Guauu

Copper Contributor

What's the difference between LOOKUP and XLOOKUP?

 

I found the answer above, but I can't delete this comment.

Deleted
Not applicable
Super! Great improvement! I'm a fan of this function.
Copper Contributor

@JoeMcDaid 

Can XMATCH be modified to search a rectangular Range

Lets say D is the name of a Table then

=XMATCH("X",D,0) = so return an array of Row and Column Cor ordinates of the string X 

The current way of doing it is rather complex assuming D starts from Row 2

MAX((D="X")*ROW(D)-1)*{1,0}+MAX((D="X")*COLUMN(D))*{0,1}

Thanks Sam

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