• 550K Members
• 5,867 Online
• 659K Conversations
Microsoft

# Announcing XLOOKUP

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

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:

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:

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

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

Occasional Contributor

Excited! ☺️

Super Contributor

What if i want more than one result from different columns

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

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

Regular Visitor

@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

Occasional Visitor
Hi All, is there a way to automatically push down the rows with values after executing transpose of the values on top of it?
Senior Member

nice

Occasional Visitor
Awesome!
Trusted Contributor
Awesome! So the first letter of the function name "X" stands for extended or extreme?
Occasional Visitor
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

Senior Member

@Joe McDaid 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).

Visitor

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.

Occasional Visitor

@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?

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.

Senior Member

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

New 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 @Joe McDaid 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.

Occasional Visitor

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.

MVP

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

Contributor

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

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

MVP

@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

Established Member

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]

Senior Member

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?

Senior Member

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

Frequent Visitor

This is fantastic!

Frequent Visitor

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.

Super Contributor

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

Occasional Contributor

Hi @Joe McDaid, Excel Team and fellow Community Members!

Thank you @Joe McDaid 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.

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.

Visitor

Thats super eXciting

Super Contributor

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

Occasional Visitor

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.

Occasional Visitor

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.

Occasional 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?

Frequent Visitor

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.

Super Contributor

@jtrustees

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

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.

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.

Visitor

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

Super Contributor

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.

MVP

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

Occasional Visitor

When will xlookup and xmatch become available?

Occasional Visitor

Great when i use this formula

Occasional Visitor

Guauu

Visitor

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

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