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

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Download the Excel File

Here is the file I use in the video.

XLOOKUP Explained.xlsx (368.3 KB)

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! 😀🙌

XLOOKUP Exact Match Simple Example

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.

XLOOKUP vs VLOOKUP Separate Lookup and Return Arrays Ranges Insert Delete Columns

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 Defaults to Exact Match Mode

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.

XLOOKUP Horizontal Lookup

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

XLOOKUP to the Left

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!

XLOOKUP Partial Match with Wildcard Characters

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.
XLOOKUP Single Wildcard Character for Misspelled Names

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

XLOOKUP Search in Reverse Order Last to First with Search_Mode Argument

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.

Pros or Advantages

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).
  7. Returns a range instead of a value (advanced nested formulas).

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.

XLOOKUP is Not Backward Compatible

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?

Please leave a comment below with answers to either or both of the following questions:

  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! 🙂

39 comments

Your email address will not be published. Required fields are marked *

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

  • 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?

  • 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! (-:

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

  • 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

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

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

  • 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! πŸ™‚

  • Congratulations on your newest future Excel geek! Thank you also for all your valuable posts.

    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.

  • 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. πŸ™‚

  • 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. XLOOKUP Copy Formula to Right with Relative References

      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. πŸ™‚

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

    • 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!

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

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

  • 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!

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

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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly