How does the XLOOKUP function work in EXCEL?
What is a function in EXCEL?
XLOOKUP is a new function in Excel that allows users to perform various types of lookups in a simple and flexible way. XLOOKUP replaces the older functions VLOOKUP and HLOOKUP, which have some limitations and drawbacks. What is XLOOKUP function in EXCEL?
It might seem complicated but is actually not. Before going ahead with the explanation, you can watch this Short video to have a taste of this function.
HOW DOES XLOOKUP WORK?
The first three arguments are required, and the last three are optional. The arguments are:
lookup_value: The value to search for in the lookup_array.
lookup_array: The array or range to search for the lookup_value.
return_array: The array or range to return the corresponding value from.
if_not_found: The value to return if the lookup_value is not found in the lookup_array. If omitted, the function returns #N/A.
match_mode: The type of match to perform. The options are:
0: Exact match (default).
-1: Exact match or next smaller value.
1: Exact match or next larger value.
2: Wildcard match, where * matches any sequence of characters and ? matches any single character.
search_mode: The direction and method of search. The options are:
1: Search from the first item to the last item (default).
-1: Search from the last item to the first item.
2: Perform a binary search, which requires the lookup_array to be sorted in ascending order.
-2: Perform a binary search, which requires the lookup_array to be sorted in descending order.
XLOOKUP VS VLOOKUP & HLOOKUP
XLOOKUP has several advantages over VLOOKUP and HLOOKUP, such as:
XLOOKUP can look in any direction, not just from left to right or from top to bottom. This means that the lookup_array and the return_array can be in any position relative to each other, and the return_array can have multiple columns or rows.
XLOOKUP does not require a column or row index number to specify the return_array. This means that the formula is simpler and less prone to errors, especially when new columns or rows are inserted or deleted.
XLOOKUP defaults to an exact match, which is more intuitive and common than an approximate match. This means that the user does not need to specify the fourth argument of VLOOKUP or HLOOKUP, which can be confusing and misleading.
XLOOKUP allows the user to customize the behavior of the function when the lookup_value is not found, by using the if_not_found argument. This means that the user can return a specific value, such as zero, blank, or a custom message, instead of #N/A.
XLOOKUP supports wildcard characters, such as * and ?, to perform partial or fuzzy matches, by using the match_mode argument. This means that the user can search for values that contain, start with, or end with a certain text, or that match a certain pattern.
XLOOKUP can perform faster and more efficient searches, by using the search_mode argument. This means that the user can choose the optimal search method for the data, such as a binary search for sorted data, or a reverse search for the last occurrence of a value.
CONCLUSION
In conclusion, XLOOKUP is a powerful and versatile function that simplifies and enhances the lookup capabilities of Excel. XLOOKUP solves some of the problems and limitations of VLOOKUP and HLOOKUP, and offers more options and flexibility for the user. XLOOKUP is a welcome addition to the Excel function library, and a must-learn for any Excel user.