XLOOKUP is a function in Microsoft Excel that allows you to look up a value in a range of cells and return a corresponding value from a different range of cells.
It is similar to Excel's VLOOKUP function, but has additional capabilities and is considered to be more flexible and powerful.
XLOOKUP Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The lookup_value (required) is the value that you want to find in the lookup array.
The lookup_array (required) is the range of cells that you want to search for the lookup value.
The return_array (required) is the range of cells that contains the values that you want to return.
The [if_not_found] (optional) argument is optional and specifies the value to return if the lookup value is not found in the lookup array.
The [match_mode] (optional) argument is optional and specifies how the XLOOKUP function should compare the lookup value to the values in the lookup array.
Specify the match type:
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 where *, ?, and ~ have special meaning.
The [search_mode] (optional)
Specify the search mode to use:
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.
Here is an example of how to use the XLOOKUP function using the minimum required parameters:
=XLOOKUP(A2, A5:A9, B5:B9)
In this example, the XLOOKUP function will search for the value in cell A2 in the range of cells A5:A9. If a match is found, the corresponding value from the range B5:B9 will be returned.
If no match is found, an # N/A error* will be returned.
Comdex Training Centre 2022
See also our blog discussion on XLOOKUP Vs. VLOOKUP...
and related article on *Excel Errors
All our Excel articles can be found here