Introduction to The NEW XLOOKUP
Author: Craig Gerdes
Published: November 11, 2021
Everyone give a big & warm “Welcome!” to Craig Gerdes, one of our newer trainers! Craig is a seasoned Microsoft Office trainer and curriculum designer.
If you have used VLOOKUP in the past, you will love the flexibility of the new XLOOKUP function. The XLOOKUP adds additional functionality while also fixing some of the issues and limitations with the VLOOKUP.
In this article, I will cover the basics of the XLOOKUP function while also providing some examples of how to use it.
First, the XLOOKUP function is an improved version of VLOOKUP because it works in any direction and returns exact matches by default making it easier and more convenient.
Second, the XLOOKUP is only available to Microsoft 365 subscribers. So, if you’re using previous versions of Excel, unfortunately you won’t be able to use this function (but is a good reason to upgrade!).
Let’s review the syntax of both functions and discuss similarities and differences.
The syntax for the XLOOKUP is as follows:
The syntax for the VLOOKUP is as follows:
1. The lookup_value is the same argument for both VLOOKUP and XLOOKUP and that is the cell reference that contains the search value.
2. The table_array in the VLOOKUP is the range or table where you want to look for the lookup_value. The lookup_arrayin the XLOOKUP refers to the same thing.
3. The col_index_number in the VLOOKUP is the column number (instead of column letter) in the table_array that contains the value to return. The return_array in the XLOOKUP indicates the same thing.
4. The XLOOKUP adds an extra optional argument ([if_not_found]) for when a valid match is not found and returns the text you provide for the argument (placed inside double quotes, for example, “Not Found”. If the [if_not_found] is missing text, #N/A is returned.
5. The VLOOKUP uses 1 or TRUE for an approximate match and 0 or FALSE for an exact match for the optional [range_lookup] argument with the approximate
match being the default if nothing is specified. The XLOOKUP, on the hand, uses the optional [match_mode] argument as follows:
0 – Exact match. If none found,
return #N/A. This is the default.
-1 – Exact match. If none found,
return the next smaller item.
1 – Exact match. If none found,
return the next larger item.
2 – A wildcard match such as * or ?.
Notice that an exact match is the default option in the XLOOKUP whereas the approximate match was the default value in the VLOOKUP. This seems more logical for most users and most situations. The XLOOKUP also provides more options and thus functionality.
6. The XLOOKUP adds an extra optional argument ([search_mode]) as follows:
1 – Perform a search starting at
the first item. This is the default.
-1 – Perform a reverse search
starting at the last item.
2 – Perform a binary search that
relies on lookup_array being sorted in ascending order. If not
sorted, invalid results will be returned.
-2 – Perform a binary search that
relies on lookup_array being sorted in descending order. If not
sorted, invalid results will be returned.
NOTE: Check out the differences in the formulas for D4 and D5.
NOTE: Review the optional argument for the 3rd lookup situation and the formulas for E13 and F13.
NOTE: Remember if nothing is entered for the optional match arguments, with a VLOOKUP it will be an approximate match but with the XLOOKUP, the default argument is an exact match.
NOTE: Pay attention to the differences in the formulas for how you want to search.
NOTE: Notice the limitations with the VLOOKUP and the HLOOKUP functions. See how much easier the XLOOKUP is in contrast to the INDEX/MATCH/MATCH.
Compared to the traditional VLOOKUP, XLOOKUP has many advantages. Here’s a list of some of the top benefits:
1. Vertical and horizontal lookup. The XLOOKUP function gets its name due to its ability to look up both vertically and horizontally. (See Example 5)
2. Look in any direction: right, left, bottom or up. While VLOOKUP can only search in the leftmost column and HLOOKUP in the topmost row, XLOOKUP has no such limitations. (See Example 5)
3. Exact match by default. In most situations, you will be looking for an exact match, and XLOOKUP returns it by default (unlike the VLOOKUP function that defaults to approximate match). (See Example 3)
4. Search in reverse order. Earlier, to get the last occurrence, you had to reverse the order of your source data. Now, you simply set the search_mode argument to -1 to force your XLOOKUP formula to search from the back and return the last match. (See Example 4)
5. If error functionality. XLOOKUP incorporates this functionality in the if_not_found argument allowing to output your own text if no valid match is found. (See Example 2)
6. Column insertions/deletions. One of the issues with VLOOKUP is that adding or removing columns breaks a formula because the return column is identified by its index number. With XLOOKUP, you supply the return range, not index number, so you can insert and remove as many columns as you need without breaking anything.
7. Better performance. VLOOKUP could slow down your worksheets because it includes the entire lookup table in calculations, which results in processing far more cells than needed. XLOOKUP handles only the lookup and return arrays that it truly depends on.
For more information about the XLOOKUP function and other formulas, take an Excel Class from Training Umbrella. For course descriptions and dates, see our website at https://trainingumbrella.com/. In addition, if you would like more information about other training classes available, contact a us at 913-438-3400 for assistance.