The 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 semi-annual 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".