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:

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

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