Raise your hand if you love VLOOKUP? Me too! We Excel users have grown to love VLOOKUP over the years. But, XLOOKUP offers some advantages. XLOOKUP? That’s not a typo. Depending on your version of Excel and when you are reading this, you may see this function immediately, or it may be a while before it arrives. But either way, it is a goodie 🙂 Thanks Bill J for the scoop on this, and Gary Z for the heads-up!
In summary, it is designed to return a related value, just like VLOOKUP … but it addresses some of the limitations of VLOOKUP and provides many additional options. Here is a screenshot of the Insert Function dialog which provides a description (and proof it exists):
Note: at the time I’m writing this, XLOOKUP is available in O365 w/Insiders Fast update channel. Depending on your version of Excel, you may not see it in your function list.
Let’s dig into the arguments:
=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value – value to find (same as VLOOKUP)
- lookup_array – lookup column (different than VLOOKUP)
- return_array – return column (different than VLOOKUP)
- [match_mode] – 0 for exact match by default + more (different than VLOOKUP)
- [search_mode] – 1 for first-to-last + more (different than VLOOKUP)
Let’s talk about these arguments in a bit more detail.
Lookup_value is similar to VLOOKUP, and represents the value we are trying to find.
Lookup_array represents a big change from VLOOKUP. Instead of defining the entire two-dimensional range (or table_array), we can define a single column. We specifically identify the lookup column range. This means that column order doesn’t matter anymore …. yay! The lookup column can be anywhere in the table.
Return_array is another big difference from VLOOKUP. Instead of defining the return column by index number (col_index_num), we can select the return column range. This is huge because it means if we insert a new worksheet column between the lookup and return columns, nothing breaks … yay! Plus, it can be located anywhere in the table, including to the left of the lookup column 🙂
[match_mode] is an optional argument, and defaults to 0 if omitted. That means that if you don’t specify it, it will perform an exact match. This is different than VLOOKUP which defaults to doing a range lookup. With VLOOKUP, we can choose True or False, but XLOOKUP offers more options:
The wildcard character match is more of what we think about when we want to do a partial match. To use this option, we just add a wildcard character like asterisk (*) to the lookup value.
[search_mode] is an optional argument, and defaults to 1 when omitted. This replicates the behavior of VLOOKUP where it tries to find a match by starting at the top and working down.
We can also search from the bottom up, which is a nice option.
XLOOKUP starts with the solid foundation of VLOOKUP, and then enhances it. It is like the VLOOKUP we’ve always wanted and dreamed about 🙂
XLOOKUP will help improve our workbooks by eliminating errors and providing more flexibility. Thank you Microsoft!!
Tip to job-seekers:
Here’s a tip for you if you are interviewing for a job and the interviewer asks you “Do you know Excel? Well, do you know VLOOKUP?” You answer like this: “Yes I know VLOOKUP … but I prefer XLOOKUP. You do know about XLOOKUP, right?” … and blow their mind!!
If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.
Want to learn Excel?
Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)