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:

The XLOOKUP function has six arguments:

1. 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.
2. lookup_array: The array or range where the lookup_value is to be found.
3. return_array: The array or range where the desired data is located.
4. 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.

5. 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.

6. 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":

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".