# Long live the King! Microsoft announces XLOOKUP, successor to the iconic VLOOKUP function

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 `VLOOKUP`

function, then you’ve used the `INDEX/MATCH`

.

Credit to Joe McDaid for announcing this in the Excel Blog

**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:

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

.**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.**Cannot search from the back:**If you want to find the last occurrence, you need to reverse the order of your data.**Cannot search for next larger item:**When performing an “approximate” match, only the next smaller item can be returned and only if correctly sorted.**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,[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:

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