Xlookup
20230522The XLOOKUP function is used to find and return an item's corresponding value in a range or array. This function was introduced in the August 2020 semiannual update of Office 365 to replace older lookup functions like VLOOKUP and HLOOKUP, offering more versatility and fewer limitations.
Here's the syntax for the XLOOKUP function:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The XLOOKUP function has six arguments:
 lookup_value: The value you want to look up. This could be a number, text, a logical value like TRUE or FALSE, a name, or a reference.
 lookup_array: The array or range where the lookup_value is to be found.
 return_array: The array or range where the desired data is located.
 if_not_found (optional): The value to return if the lookup_value is not found in the lookup_array. This takes the place of an IFERROR that would have to be placed before the formula.

match_mode (optional): Defines the way to match the lookup_value with values in the lookup_array. This can be set to:
 0: Exact match. If none found, return an error (this is the default).
 1: Exact match. If none found, match with the next smaller item.
 1: Exact match. If none found, match with the next larger item.
 2: Wildcard match, where "?" matches any single character and "*" matches any sequence of characters.

search_mode (optional): Defines the way to perform the search in the lookup_array. This can be set to:
 1: Search from first to last (this is the default).
 1: Search from last to first.
 2: Binary search in ascending order.
 2: Binary search in descending order. For binary search, the lookup_array should be sorted.
Here's an example of how you might use the XLOOKUP function. Suppose you have a list of products in column A (cells A2:A6) and their corresponding prices in column B (cells B2:B6), and you want to find the price of a product named "Apple":
=XLOOKUP("Apple", A2:A6, B2:B6, "Not found")
If "Apple" is in cells A2:A6, this formula would return its corresponding price from column B. If "Apple" is not found, it would return "Not found".