If you do data analysis, then you’ve used Excel. If you use excel, then you know the VLOOKUP function. If you’ve run into the limitations of the VLOOKUPfunction, then you’ve used the INDEX/MATCH.

Credit to Joe McDaid for announcing this in the Excel Blog

techcommunity.microsoft.com/t5/Excel-Blog/A..

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

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 XLOOKUPin favor of VLOOKUPand 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])

Availability Notes — When can i start using XLOOKUP?

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

Learn More

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

• XLOOKUP help

• XMATCH help

There are tons of good discussions in the comment section as well, so make sure you read through that!

Here are more examples from Bill Jelen (MrExcel)

Excel XLOOKUP, First Look!

Find the Last Match

XLOOKUP allows you to begin your search at the bottom of the data set. This is great for finding the last match in a data set.

Look to the Left

Like LOOKUP and INDEX/MATCH, there is no hassle looking to the left of the key with XLOOKUP.

Where you would have used =INDEX($E$3:$E$30,MATCH(A2,$F$3:$F$30,0)) previously, you can now use =XLOOKUP(A2,$F$3:$F$30,$E$3:$E$30)

Speed Improvements of XLOOKUP

In the example above, the VLOOKUP has to recalculate if anything in the lookup table changes. Imagine if your table included 12 columns. With XLOOKUP, the formula will only recalc if something in the lookup array or results array changes.

In late 2018, the VLOOKUP algorithm changed for faster linear searches. The XLOOKUP maintains the same speed improvements. This makes the linear and binary search options nearly identical. Joe McDaid says there is no significant benefit to using the binary search options in Search_Mode.

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.

Multiple Columns of XLOOKUP

Need to do 12 columns of XLOOKUP? You could do it one column at a time…

Or, thanks to Dynamic Arrays, return all 12 columns at once…

Approximate Lookups No Longer Have to Be Sorted

If you need to find the value just less than or just greater than the lookup value, the tables no longer have to be sorted.

Or to find the next larger value:

The Only Disadvantage: Your Co-Workers Won’t Have It (Yet)

Due to the new policy of Flighting, only some small percentage of Office Insiders have the XLOOKUP feature today. It could be a while until the function is widely available and even then, it will require an Office 365 subscription. (Dynamic Arrays have been out since September 2018 and still have not rolled out to General Availability.)