Bottom line: Learn how the new XLOOKUP function can replace VLOOKUP or INDEX MATCH.
Skill level: Beginner
Video Tutorial
Download the Excel File
Here is the file I use in the video.
XLOOKUP-ExplainedDownload
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.
Pros or Advantages
Here is the list of advantages for XLOOKUP that I shared in the video above.
- Defaults to exact match.
- It only requires three arguments, instead of four for VLOOKUP or INDEX MATCH.
- Works both vertically and horizontally.
- One function instead of two, compared to INDEX MATCH.
- Can do partial match lookups with wildcard characters (4th argument = 2).
- Can do lookups in reverse order (5th argument = -1).
- Returns a range instead of a value (advanced nested formulas).
Cons or Drawbacks
There are also a few potential issues to be aware of.
- Additional [optional] arguments can make the function look overwhelming to new users.
- Returns a #VALUE! error if the lookup and return arrays are not the same length. I explain this in the video above.
- It can be time-consuming to select two ranges with the mouse, especially when you have thousands of cells in the arrays.
- You must remember to make both the lookup and return ranges absolute references (F4 on the keyboard) if copying the formula down/across.
- 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?
Please leave a comment below with answers to either or both of the following questions:
- What do you think about XLOOKUP? Are you excited? Will it be easier to use AND teach your co-workers?
- 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! 🙂
