Announcing XLOOKUP

[JoeMcDaid]

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:

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:

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

Learn More

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

• XLOOKUP help
• XMATCH help

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

[KanwalNo1]

@Sergei Baklan @JoeMcDaid Thanks Sergei ! That is indeed a wonderful explanation !

What I intend to do is to check the value of the nth instance, which I can control via some external reference. E.g.,

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

Here Instance No is the value to be returned e.g., For A11 by default the value to be returned is 101 and 201, but if we choose to return the 2nd instance then the result will be 104 and 204.

Also if Instance No option is ALLValues and Delimiter is comma then the formula result would be 101,104,107.

Instance No is useful if we intend to use the formula result in another formula.

Filter won't work here.

[Peter Bartholomew]

@KanwalNo1 

I agree with you (see my post a few above 01-08-2020 02:43 AM).   This thread is now rather long but I recollect that others have already made such requests.  The obvious place is to repurpose the search down / search up parameter but that is only possible if the bisection search values of the parameter are not committed.

At present, what you can return is limited to the first and last instances

= TEXTJOIN( ", ", TRUE, XLOOKUP( Lookup_Value, Lookup_Array, Return_Array, , , {1,-1} ) )

[Sergei Baklan]

@Peter Bartholomew , back to my sample that's like

=TEXTJOIN(",",1,INDEX(FILTER(Table1[C],Table1[A]=$H$2),{2,3}))

[KanwalNo1]

@Sergei Baklan Looks Like Magic ! Thanks Sergei !

@JoeMcDaid Sir, Why can't something like this cannot be incorporated in XLookup itself. We can control the output for {2,3} parameter by referring to an external cell value. Please explore the possibility for the same Sir !

[Peter Bartholomew]

Hi @Sergei Baklan 

I agree you had already made the point that TEXTJOIN does a perfectly good job.   The point I set out to make is that XLOOKUP is already capable of returning the first and last match as an array by using {1,-1} as the final parameter.  Unfortunately {2,-2} does not return the 2nd and 2nd from end as one might guess but instead sets up binary searches, something that @JoeMcDaid  stated has no obvious purpose given that the function is implemented so that it uses bisection search without user intervention.

[KanwalNo1]

I am not able to understand what is Binary Search in Xlookup ?

[Arin Komins]

delighted that this finally hit monthly channel...although can't start using it more widely until it is broadly released (at least in any workbook that I am sharing with others...)

[KanwalNo1]

@JoeMcDaid @Sergei Baklan @Peter Bartholomew I am not able to understand what is Binary Search in Xlookup ?

[Peter Bartholomew]

@KanwalNo1 

It also applies to MATCH and VLOOKUP.  There, 'exact search' ran through each value in the lookup range until a match is found.  For an range of 1000 cells this, on average, requires 500 comparisons.  The binary (approximate) search applied to sorted data.  The algorithm examines the value half way down the list and, depending upon its value, discards one half of the list or the other.  This halving takes place until a closest match is found, typically requiring 10 comparisons for 1000 cells.

XLOOKUP has been written to take advantage of the efficiency inherent in binary searches even to the extent of sorting the data in memory where this leads to a performance gain.  Somewhere early on in this thread I remember @JoeMcDaid stating that he could not envisage circumstance in which the user would gain by explicitly selecting the search algorithm by using the final parameter setting of ±2.

[JoeMcDaid]

@Peter Bartholomew Correct, binary search has some limited application in very high performance use cases.  This is what VLOOKUP did for the approximate match and why it would give unexpected results for unsorted lists. With XLOOKUP, we want to give all users a viable path to move off VLOOKUP - even high performance ones - which is why XLOOKUP supports binary search.

Generally I would advise against its use except by expert users and, even so, I'd expect them to use it extremely sparingly because of the risk. XLOOKUP has internal optimizations so that, by default, it's extremely fast both on sorted and unsorted ranges so the speed benefits are often immaterial.  

[KanwalNo1]

@JoeMcDaid @Peter Bartholomew @Sergei Baklan 

I am not sure how many of other users felt the same, but whenever I use the XLookUp function, the system freezes while trying to Select the lookup_array and return_Array residing in other sheets of the Same workbook / other workbooks.

[Peter Bartholomew]

@KanwalNo1 

It is my experience that the insider beta release versions of Excel can be somewhat fragile and crash with no apparent reason.  On the other hand, I have not experienced the problem you describe.  I do need to have the source workbook open to avoid a #REF! error but I am not sure whether that is the way I reference the source table or whether it is an intrinsic property of XLOOKUP.

[Sergei Baklan]

@KanwalNo1 , didn't see that. How do you select these two arrays?

[KanwalNo1]

Hi Sergei,

In the normal way, while we type formula.

After writing comma, I go to other sheet and select the region for Lookup_array and Return_array.

I am keeping a sharp eye on whenever this is happening.

Give me sometime. I will revert when I observe a pattern.

[Aurelie_Silvrants]

Hi all,

I am also very excited about the XLOOKUP Function, here in Belgium it is nog communly used yet I have the impression.

However since I have been using it instead of VLOOKUP I am having trouble with my Excel files. I keep getting

"Calculating (8Threads):xx%"

Somethimes it dissapears after a few seconds however in somethimes it can take hours... or just block.

It is the biggest issue offcourse when the files are large however also small files have the same issue.

And if I replace the Xlookup by the old Vlookups the issue seems to dissapear...

Did anyone have the same issue? Is there a way to solve it?

I really love using it so would be very dissapointed if I would have to go back to Vlookup because of this :)

Thank you!

Regards

Aurélie

[garnhartwhidbeynet]

How do I get rid of cell notation?

[Hans Vogelaar]

@garnhartwhidbeynet 

What do you mean by "cell notation"?

[Doonzu_korean]

@Peter Bartholomew 

As for the binary search principle of the MATCH, VLOOKUP, and LOOKUP functions, I agree with you. However, I have one question. And I think there is no answer other than the logic I have uncovered. Binary search reduces the number of searches by moving in opposite directions to the large and small search values in random cells about half of the range to be searched. However, when finding the last data, the 1 option of MATCH specifies a value greater than the range, and the -1 option specifies 0 if the value in the range is not negative, and the last value is found.

However, in the data of #NA, #NA, #NA, 1, #NA, 1, when 2 is the search value, the first 1 should be found in the binary search logic, but Excel returns the last 1. So the logic I came up with is that even if it is a binary search, when you search from the very end and then try the binary search. This has nothing to do with whether the cells are aligned or not.
What do you think about this?