The XLOOKUP function in excel usually lookup or search for a single value in an array or range. XLOOKUP is the newest member of Excel lookup function family. You may already know its siblings – VLOOKUP, HLOOKUP, INDEX+MATCH, LOOKUP etc.
XLookup Function in Excel
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.
Must Read: Triple VLookup with Index Match function in Excel
Use the XLOOKUP function to find things in a table or range by row. For example, look up the category of a customer part by the code number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.
Syntax
This function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Required Arguments:
lookup_value : The value to search for
If omitted, XLOOKUP returns blank cells it finds in lookup_array.
This function separates the lookup array and the return array into two arguments. The lookup array should house the value Excel will search for and the return array argument will house the value to be returned.
Must Read: Lookup Multiple Fields of Data with Excel VLOOKUP
lookup_array : The array or range to search. The lookup array can be to the right or left of the return array.
return_array : The array or range to return.
Optional Arguments:
[if_not_found] : Where a valid match is not found, return the [if_not_found] text you supply. It’s an optional argument. If a valid match is not found, and [if_not_found] is missing, #N/A is returned.
[match_mode] : Specify the match type. It’s also an optional argument, where you have multiple choices, which is given below.
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.
[search_mode] : Specify the search mode to use. It is also an optional argument. This argument also has multiple options.
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.
I hope you like this article. If you have any queries then please comment us.