# The New XLOOKUP Function for Excel + Video Tutorial

Bottom line: Learn how the new XLOOKUP function can replace VLOOKUP or INDEX MATCH.

Skill level: Beginner

## Video Tutorial

Here is the file I use in the video.

Note: The formulas will only work if you are on a version of Excel that supports XLOOKUP. Currently a portion of users on the Office 365 Insiders Fast channel. See the Compatibility section below for details.

## What is XLOOKUP?

On August 28th, 2019, Microsoft announced XLOOKUP as the successor to VLOOKUP. It took 34 years, but we now have a function that is easier to use and will help prevent some of those frustrating errors we get with VLOOKUP. Yay! 😀🙌

It does have a few potential issues/drawbacks, which I also explain in this post.

The job of XLOOKUP is to find a value in a row (column) and return a value from an adjacent column (row) where a match is found. It's the same basic principle as VLOOKUP but includes some cool new features.

## XLOOKUP's Signature

Here is the signature (arguments) for XLOOKUP:

`=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])`

It looks a bit scary at first, but only the first three arguments are required. The last two in [square brackets] are optional.

## How is it Different from VLOOKUP (and HLOOKUP)?

There are four major differences between XLOOKUP and VLOOKUP.

### 1. Two Separate Arguments for Lookup and Return Arrays

The first is that the lookup_array (range to look in) and return_array (range to return a value from) are two separate arguments. This is similar to how INDEX MATCH formulas work.

With VLOOKUP we just have the single table_array that contains both the column to look in and return column. The column index number is used to specify which column to return.

VLOOKUP's table_array can cause errors in our formulas when we insert/delete columns in that range. XLOOKUP prevents this error by splitting the lookup and return ranges into two different arguments.

### 2. Defaults to Exact Match

Probably 99% of the time we use VLOOKUP, we do an exact match. This means we have to specify the last argument (range_lookup) as FALSE or zero because VLOOKUP's default is an approximate match.

XLOOKUP's default is an exact match. This means we only have to specify three arguments to do an exact match lookup.

XLOOKUP does have an option [match_mode] argument that allows us to do approximate/closest match OR use wildcard characters. It's really cool and I explain more about wildcards below.

### 3. Horizontal Lookups

XLOOKUP can also do a horizontal lookup. We don't need a separate function like HLOOKUP for this. You just specify single rows (instead of columns) for the lookup and return arrays.

### 4. Look to the Left (or Above)

Since the lookup and return arrays are separate ranges, we can use XLOOKUP to return a value to the left of the lookup range. This is something you can't do with VLOOKUP. (Technically you can with CHOOSE but INDEX MATCH is easier at that point).

You can also use XLOOKUP to return a value from a row above the lookup range when doing a horizontal lookup.

## What Other Cool Features Does XLOOKUP Have?

So far it looks like XLOOKUP is very similar to INDEX MATCH. The major difference is that we can perform the same lookup in one function (XLOOKUP) instead of two (INDEX MATCH).

However, Microsoft gave us some additional features with XLOOKUP that make it even more useful.

### 1. Partial Matches with Wildcards

XLOOKUP's fourth argument is [match_mode]. It has four options.

The first three are similar to the MATCH function (exact match, less than, greater than).

The fourth option, Wildcard character match is pretty magical!

It allows us to perform partial match lookups. In the example below I have a list of last names that I want to lookup in a list of full names (first & last name).

With XLOOKUP we can use the * (asterisk) or ? (question mark) characters in the lookup value for wildcards.

• An asterisk represents any number of characters.
• A question mark represents any single character.

If you are looking for an asterisk or question mark, then you can use the tilde character ~ before the search character.

You then specify a 2 for the match_mode argument to turn on wildcard character match.

This will really allow us to get creative with partial match lookup formulas, and I can't wait to see what awesome solutions the Excel community comes up with. I will do more posts in the future as well, so leave a comment below if you have questions on partial match lookups with XLOOKUP.

### 2. Search in Reverse Order

Another awesome feature of XLOOKUP is the ability to search in reverse order. The function's fifth argument is [search_mode].

The default option is 1 to Search first-to-last. We can specify a -1 to Search last-to-first. The search is performed in reverse order; bottom-to-top or right-to-left.

This is great for finding the last occurrence of an item in a list and returning a matching result. In the example above I use XLOOKUP to find the last sale that was made on a specific date.

This is just one simple example, and there are a TON of use cases where this will come in handy. Again, leave a comment below if you have questions/suggestions on how to use this feature.

## Pros & Cons of XLOOKUP

XLOOKUP is an amazing new function and definitely has some major advantages over VLOOKUP and INDEX MATCH. It does have some drawbacks too.

Here is the list of advantages for XLOOKUP that I shared in the video above.

1. Defaults to exact match.
2. It only requires three arguments, instead of four for VLOOKUP or INDEX MATCH.
3. Works both vertically and horizontally.
4. One function instead of two, compared to INDEX MATCH.
5. Can do partial match lookups with wildcard characters (4th argument = 2).
6. Can do lookups in reverse order (5th argument = -1).

### Cons or Drawbacks

There are also a few potential issues to be aware of.

1. Additional [optional] arguments can make the function look overwhelming to new users.
2. Returns a #VALUE! error if the lookup and return arrays are not the same length. I explain this in the video above.
3. It can be time-consuming to select two ranges with the mouse, especially when you have thousands of cells in the arrays.
4. You must remember to make both the lookup and return ranges absolute references (F4 on the keyboard) if copying the formula down/across.
5. You must use nested functions to do a 2-dimensional lookup. Can use two XLOOKUPs or INDEX MATCH.

My new free VLOOKUP Assistant tool can help with #'s 2, 3, and 4 above. It makes it much faster and easier to create lookup formulas and will also work with XLOOKUP (coming soon).

## Availability & Compatibility

So you're probably wondering how you can get your hands on this new Excel awesomeness…

XLOOKUP is still in “preview” on the Insiders Fast channel for Office 365. In the coming months, it will be released to the other Office 365 channels.

XLOOKUP (and it's new little bro XMATCH) will ONLY be available to Office 365 subscribers. Here is a link to the XLOOKUP help page that shows the versions it is available on.

It will NOT be available on Office 2019 or any previous versions of Office.

Here is a link where you can learn more and join the free Office Insiders program if you want to get XLOOKUP now. Note: The update has only been released to a random portion of users on Insiders Fast, but should be made available to everyone on that channel soon.

### Is XLOOKUP backward compatible?

Unfortunately not. 😢

If you are on Office 365 and use XLOOKUP in your files, then anyone you send your files to will also need to be on Office 365, AND have an updated build that supports XLOOKUP.

This means it will be ok to use in an organizational environment where all your co-workers are on Office 365.

It won't be a good idea to use XLOOUP if you share your files with contractors, friends, or people outside your organization that might not be on Office 365.

When a person that doesn't have XLOOKUP opens the file they will see _xlfn. in front of the function name. The value in the cell will still be there. It will return a #NAME? error when the cell (or file) is recalculated.

## Conclusion

XLOOKUP is an awesome function! It solves a lot of the pain points associated with VLOOKUP and INDEX MATCH. It also has some cool new features like partial match lookups with wildcards and searching in reverse order.

I think it's biggest drawbacks will be availability and backward compatibility. At least for the next 7 to 10 years as organizations slowly upgrade to Office 365… 😉

### What do you think?

1. What do you think about XLOOKUP? Are you excited? Will it be easier to use AND teach your co-workers?
2. What do you want to use XLOOKUP for? What problems are you curious if it can solve with the new partial match, reverse order features, and dynamic array features?

Thank you! 🙂

• Rick says:

1. I am excited to use the new XLOOOKUP function in Excel!
Yes, it will be easier to use and teach co-workers.

2. Love the partial match and dynamic array options.

3. Is XLOOKUP more efficient (take less resources) than VLOOKUP?
Currently, I use a lot of VLOOKUP formulas and this function bogs down my
files with waiting for the spreadsheet to recalculate on thousands of records.

• Hey Rick,
Thanks for answering the questions! XLOOKUP should be more efficient than VLOOKUP. At least in some cases.
The table_array in VLOOKUP can potentially slow down the calc since it spans multiple columns that are not necessarily part of the calculation. Since XLOOKUP has two separate arguments for the lookup and return arrays, it won’t suffer from this issue.
Microsoft also improved the performance of VLOOKUP last year. Here is an article that explains more about it.
I hope that helps.

• Kyle says:

These office 365 exclusive updates are frustrating and the backwards compatibility issues it creates. I hope that it is open to all Excel users sometime in 2020.

I agree it is it’s biggest con.

• Thanks Kyle! I feel your pain, and wish it could be open to all users as well…

• Jessica says:

I agree. We have 365 at work but still do not have the XLOOKUP function available.

• Hi Jon, XLOOKUP is awesome. Excellent article! And like Kyle mentioned, I would say that the compatibility issue is by *far* the #1 biggest Con, but that’s an issue with most things Excel introduces. On the other hand, when Google implements its version of the new XLOOKUP function, it will end up being immediately available to almost all users. On the plus side, content written about VLOOKUP and INDEX-MATCH will still be useful for many years because so many people will need to continue using it (or be forced to upgrade).

• Thanks Jon! I completely agree. Microsoft is in a transition period right now with the desktop software and subscription model. With a 34 year history, it’s going to be a very slow transition.

These new features are great, but not sure how convincing they are for organizations to upgrade immediately. As you said, Google doesn’t suffer from these issues being entirely web-based, and they will probably be the biggest beneficiaries from updates to Excel…

It’s a sad reality at this point, but I hope that changes in the future as more organizations make the transition to Office 365.

I will still be covering VLOOKUP and INDEX MATCH. Next week we have an article on making VLOOKUP more dynamic, for those that don’t have XLOOKUP yet.

Thanks again for your insights! I appreciate all that you do too!

• DC Pear says:

This release about XLOOKUP after I finally figured out how to do a VLOOKUP from your post a few weeks ago! LOL!

I agree, it is too bad about the backwards compatibility issues. Still looks cool though.

• Haha thanks DC! We will never run out of things to learn or problems to solve with Excel. Especially when Microsoft gives us new features!

• Mickael says:

Great post! Thanks for sharing the good news and congratulations to you and your family!

• Mickael says:

Jon,

For your Cons #2, isn’t it a good general practice to select the entire column versus a specific range within that column (ex: F:F vs \$F\$1:\$F\$200)?

• Great question, Mickael! It depends on the scenario, but in general, I’m NOT a fan of whole column references.

Some older functions do not handle them well, and they have the potential to slow down the calculation.

Newer functions like XLOOKUP handle this pretty well, and will typically still run efficiently when whole column references are used.

For me, it’s more about the use case and how the worksheet is setup. You obviously have to setup the sheet so that NOTHING is above or below the actual used range.

You also have to make sure other users know that, if you are distributing your file. I’ve experienced many cases where other users don’t know that whole column refs are being used and they use cells above or below the used range to do some calculations. This can cause incorrect results in the formulas.

So, I’m not saying to never use whole column refs. But I do advise being careful as to WHEN you use them.

I prefer using Excel Tables and their structured reference formulas. These references are easier to write or select, and they automatically expand with the Table. They do come with their own set of drawbacks. The biggest being that other users might not be familiar with structured references and also get confused.

I don’t believe there is a clear winner. It all comes down to the use case.

• jaimal Patel says:

Hi Jon,

do you know when will xlookup and xmatch come to excel 365?

Thanks

Jaimal

• Hi Jaimal,
It’s currently on the Insiders Fast Channel for Office 365. I do not know when it will be released to the other channels. I believe it will depend on how the testing goes on Insiders Fast… I will make an announcement on our email newsletter when it is generally available.

Thanks!

• Steve says:

Congrats and thanks for a really useful post. I got the update to Excel while I was watching – good timing!

One other potential disadvantage is that it doesn’t spill like dynamic arrays when using wildcard matching. You only get the first match. Filter is a possible alternative if you want to return all matches, but the formula is much more complicated.

BTW you can do a combined vertical & horizontal match according to Microsoft – see Example 3 in https://support.office.com/en-gb/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929?ui=en-US&rs=en-GB&ad=GB. It’s like =XLOOKUP(D2,\$B6:\$B17,XLOOKUP(\$C3,\$C5:\$G5,\$C6:\$G17)).

• Thanks Steve! I’m happy to hear you got the update!

It would be cool if XLOOKUP could spill multiple results on a wildcard. I agree that FILTER is the best option for this but also more complicated as you said.

Thanks for the suggestion on the nested XLOOKUPs. I updated the example file with a similar formula.

• David says:

Jon:

Excellent article, as usual. I look forward to release of XLOOKUP. Can you please demo how to drag the command over column heads if the data range has three additional columns “City”, “State” and “ZIP” and I want to add the three columns to the lookup range use the lookup value of Name to lookup “email”, “City”, “State” and “ZIP”?

• Thanks David! Great question! You can do this by nesting an XLOOKUP formula in the return_array argument.

I just added a sheet at the end of the file named “Relative XLOOKUP” that has an example. The formula would look like the following.

=XLOOKUP(\$A6,\$G\$6:\$G\$1005,XLOOKUP(B\$5,\$H\$5:\$J\$5,\$H\$6:\$J\$1005))

Here is a screenshot of that sheet.

You can also use an INDEX MATCH formula for this. I think INDEX MATCH is a bit easier to understand in this case, but it’s pretty cool that XLOOKUP does work.

I hope that helps. 🙂

• Luis G Polanco says:

Good Morning Jon.. First of All, Thank you for Sharing.. I think that It will take quite a while for Business Owners change their way of working and place all their Trust on the Cloud.. None of the people I deal with want to put their Financial Information on the Cloud..

Nobody want their Financial Information to appear in Wiki-Leaks of any other breach information system..

Although I can understand from your explanations this XLOOKUP will be a piece of cake, if Microsoft don´t convert or transfer to the Regular Office platform, not to much people will use it..
Again Thank you for keeping us, Your Readers Updated… Best Regards, Luis

• Hi Luis,
Thank you for the feedback. However, XLOOKUP is available on the desktop versions of Excel. Office 365 is NOT just cloud-based. It still has versions of Excel that run on the desktop for Windows and Mac. You do NOT have to store files in the cloud.

I think this is a big misconception about Office 365, and hopefully, Microsoft can do a better job of communicating this.

I hope that helps. 🙂

I am still on Office Pro 2010 and appreciate the VLOOKUP Assistant tool you have provided. Since I create Excel files for clients and others, XLOOKUP is not something I would be using. But you share so much information that I learn things I can use from these posts, even if it is not the new Excel function.

You are awesome Jon. Thank you for all you do.

• Thank you so much, Victoria! I really appreciate your support and happy to hear you are learning so much from the posts. Awesome! 🙂

• Wayne Edmondson says:

Hi Jon.. thanks for the post and congrats on the new addition to your family! With a 0 year old and a 2 year old.. good luck getting any sleep :))
Watched the XLOOKUP video you posted on YouTube and it was outstanding. I definitely agree with you on the issue of backward compatibility. It’s a thorny problem, as advanced users want to go forward, but if you share files or build solutions for legacy users, you must stick with the old at the expense of the new. This goes for X functions, dynamic array functions, etc. It is unfortunate, but I guess unavoidable, until the market coverage for Office 365 is more common. Until then, we’ll keep using all the features that make EXCEL such a great tool.. both old and new. Thanks again and Thumbs up!!

• Thanks, Wayne! I’ve realized that coffee is just a luxury until you have two kids under two. 🙂

You’re right that the back compat story is going to be an issue for years to come with Excel. It’s an unfortunate side effect of software that has been around for 34 years, with many of the 34-year-old features still used today. I think Microsoft is in a bit of a pickle trying to accommodate so many different user bases, while also competing with Google’s cloud-only solution, Sheets. I hope MS can find ways to make the transition smoother.

Thanks again! 🙂

• Rodney says:

Excellent stuff.
Just a point about Con #4, this is the same for all the LOOKUP functions so isn’t really a XLOOKUP con.

1. I think the new XLOOKUP will be easier to teach/learn as it is more intuitive as it is more explicit around what the formula is doing. Much like the INDEX MATCH formula.
2. I can see how you can determine the first and last match using the search_mode. Just wondering, if I had more than two possible matches could I pull the second or third or fourth match etc as required?
Another possible con is misaligned references. Still the same number but one starts at B2 and the other starts at G3. This is common to INDEX MATCH formula and can be hard to spot until someone notices that it is returning the value from the row below.

• Ron says:

Hey John:
You’ve done a better job than many covering the point that it is limited to 365. BUT, that limit should be in the CON list. I think it should be the #1 CON.

Unlike a lot of the new crap MS is pushing out this new command could be really be a big hit, but only if it is available in all supported versions. Things like the Stocks and Geography data types, or new chart types are nice to have, but people can get along without them. On the other hand, XLookUp() has the potential to make it easier for everyone to do lookups, but not if they can’t share workbooks with other legitimate users. Porting it to 2019 is trivial, it is probably already in place in the code, just blocked by a license check. It wouldn’t be that much more work to move it to 2016.

I understand MS’s motive for limiting it to 365, \$\$\$MONEY\$\$\$, but if they want people to use it, they have to make it available to everyone.

• Carolyn says:

Great information and explanation

• Harish says:

Thanks Jon for the leanings on XLookup.

Do you think we can do a Multiple column search easily with Xlookup?
Eg-> Let us say First Name and Last Name in 2 columns, Presently we create a new column combining both and do a vlookup on the combined formulated column. Just curious to know if this work can be minimized with Xlookup.

Regards,
Harish

• Hi, thank you for a great video explaining. I have not yet had an update with the formula, but I am very much anticipating it. I expect some difficulties because of different versions of Excel, but that will come along.

I have a question – are there any indications / comparisons on XLOOKUP performance vs VLOOKUP? As VLOOKUP can get very slow on larger data, while INDEX MATCH tends to be faster.

Hey Jon,

Great video, a really clear explanation of the new XLOOKUP function, thanks!

For me the most exciting (and useful) feature is being able to lookup results in a range to the left of the lookup_array, currently I have several spreadsheets where I have had to replicate a column further over to the left in the spreadsheet to use as the lookup_array order to return the result from the correct column.

Looking forward to more of your videos – thanks! (-:

• Glenda Little says:

Sounds interesting!

• Bob Umlas says:

You left off the fact that when using the -1 or 1 match mode, the array does NOT need to be sorted! Very cool!

• sundar says:

sir , how to get this formula to free?
because i used this formula

• Daryl D. says:

Jon, Congrats on baby Emily! She is beautiful!
Have you thought about creating a macro which would convert XLOOKUP to existing function(s) if the receiver does not have XLOOKUP available in their version of Excel?

• Rob says:

This is impossible for cases where the lookup column is not to the left of the return columns. I wouldn’t want a macro attempting to reorder the columns of my source data.

• Sandip Das says:

It is really a good formula but I can’t use it properly because I am using Microsoft office 10

• Olga says:

Congratulations for new born part of your family

• Dickson says:

Am very excited and formula is clear, hope my fellow co-workers will be happy with it. You are genius. Congrants for the baby girl, may the good Lord bless you and the entire family.

• TSWest says:

Would have been great had it been backward compatible – not upgrading to office 365 anytime soon

• VH says:

Congrats on the new baby! She is lovely. I am sort of excited about this but I know it will take a some time for my office to upgrade to Office 365. Thanks for the post and video!

• Hi Thanks for sharing XLOOKUP BUT In my excel sheet 2016
not working
kindly on this matter

Seems you missed the part about it not being available in anything but the latest builds of Excel, so it will not and never will work in your Excel 2016 sheet.

• Jorz says:

this is only avaiable in O365 license

• Matt says:

This really SUCKS why can’t Microsoft roll out these new function to all Office 2016+ users?

• David says:

John – thanks for the great tutorial. Where can I find the code for the XLookup macro you have on your My Macros tab? I don’t see it listed in the vlookup assistant file. Thanks again.

• Jessica says:

Did you ever find this? I can’t find it either.

• Simon J says:

It appears that in a newer release of Excel they have added an additional [optional argument] to the xlookup formula after “return array”, called [if not found], which now completely messes up any pre-exisiting xlookup formulas such as those in the example file. The optional arguments for match mode and search mode are now “pushed” along one step and necessitate an additional comma and/or value in the formulas in order to function.

I expect this is still considered a beta function and therefore things are likely to change until full public release.

• Jessica says:

Thanks for this tip! I was trying to figure out why I couldn’t make it work and this would be why!

• Stodge says:

Hi Jon – loving the new XLOOKUP function. I have been trying to find a way of integrating it into a great search formula I have been using.

=LOOKUP(2^15,SEARCH(D1:D20000, A1 ),F1:F20000)

This formula allows me to find the last occurrence of a key word found in the phrase in A1 within table D1:D20000 and returns the corresponding result from F1:F20000. This is great formula because it lets me look for a partial matches.

For example: A1 = “To supply CCTV system” will find the key word “CCTV” and return “Security system” being the last match in column F from the data table below.

EXAMPLE DATA TABLE
# |COLUMN D | COLUMN F
1 | alarm cctv |Security system
52 | heating system | HVAC
73 | suspended ceiling | Building infrastructure
90 | cctv survey | Site survey

Whereas if A1 had the phrase “To undertake a CCTV survey to the drain system” this would return “Site survey” from column F.

I have been trying to put the formula into a UDF but without success!

• G Lee says:

I am disgusted that MS will not include this in Office 2019.

• Tristan says:

Hi – thanks for the XLOOKUP primer. I’ve always been an INDEX MATCH person but this will speed things up a bit.

I wonder if it isn’t worth showing some examples where the data is formatted as an Excel table rather than the “range” data you’re showing. Tables make life so much easier a) Selecting the data range you want to use b) removes a lot of concerns about what to do if new columns are added/inserted etc 3) makes formala legibility much better d) prevents the possibility of misaligned ranges … plus all the other bits that tables provide eg easy column/row relocation; easy pivot chart conversion; easy color banding or rows with the ease ; … the list goes on.

Of course maybe you demo Tables in other posts – nevertheless awareness would increase if they’re shown been used when highlighting other Excel aspects.

• johnny says:

i think it is great and much simpler to use – 1 question can you use with AND
thanks johnny

• leaver says:

One typo in the article, search XLOOUP and you will find.

• James D Russell says:

I often use SUMIFS (and COUNTIFS) for the partial lookup feature when I suspect that INDEX-MATCH would be more efficient. However, when working with tables/arrays, I am often pushed into using INDEX-MATCH instead of SUMIFS. It sounds like I could start using XLOOKUP – when the rest of the world gets Excel 365.

• Jessica says:

Do you have an updated Vlookup assistant tool or file that has Xlookup? This article said back in 2019 that there would be an update and I can’t find any updates in any files on your site.

• Steve says:

no wonder i have major problems with excel not able to perform multi criteria cell condition format on whole table – instead being forced to specify one condition after another for each value separate for each column separate with errors – microsoft sucks a lot – as Win10 OS (being faulty all the time due upgrade’s) as Office in Word u cant properly rotate table only particular page and that page when in horizontal position u cant number it incorrect view and for excel – well nightmare. I regret using excel already.

Generic filters
Exact matches only

#### Excel Shortcuts List

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List