• 589K Members
• 6,337 Online
• 715K Conversations
Microsoft

# Announcing XLOOKUP

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

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

Senior Member

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

Super Contributor

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

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

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

@Sergei Baklan Looks Like Magic ! Thanks Sergei !

@Joe McDaid 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 !

Super Contributor

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 @Joe McDaid  stated has no obvious purpose given that the function is implemented so that it uses bisection search without user intervention.

Senior Member

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

Contributor

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

Senior Member

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

Super Contributor

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

Microsoft

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

Senior Member

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.

Super Contributor

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.

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