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