As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Use XLOOKUP in Excel

The XLOOKUP function in Excel is a powerful tool for looking up and retrieving information from a table or range. Here's a guide on how to use XLOOKUP.

Basic Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Parameters:

  • lookup_value: The value you want to find in the lookup_array.
  • lookup_array: The range or array containing the values to be searched.
  • return_array: The range or array containing the corresponding values to return.
  • [if_not_found]: (Optional) The value to return if no match is found. If omitted, it defaults to #N/A.
  • [match_mode]: (Optional) The match mode, which can be 0 (exact match, the default), -1 (exact or next smaller item), or 1 (exact or next larger item).
  • [search_mode]: (Optional) The search mode, which can be 1 (first-to-last, the default) or -1 (last-to-first).

Example:

Assuming you have a table in the range A2:B6 where column A contains names, and column B contains corresponding ages. You want to find the age of a person named "John."

=XLOOKUP("John", A2:A6, B2:B6, "Not Found")

This formula searches for "John" in the names (A2:A6) and returns the corresponding age from column B. If "John" is not found, it returns "Not Found."

Tips:

  1. Handling Errors:
  2. You can use IFERROR to handle potential errors. For example: =IFERROR(XLOOKUP(...), "Not Found").
  3. Wildcard Characters:
  4. You can use wildcard characters (* and ?) in lookup_value when searching. For example: =XLOOKUP("Joh*", A2:A6, B2:B6).
  5. Array Formulas:
  6. XLOOKUP can handle arrays as both the lookup_array and return_array, making it versatile for various scenarios.
  7. Default Values:
  8. If [if_not_found] is omitted, it defaults to #N/A. You can leave it blank or use an empty string if you want a blank cell instead of #N/A.

Additional Notes:

  • XLOOKUP is available in Excel 365, Excel 2019, and Excel 2016 (with the latest updates).
  • For users with Excel 2019 or Excel 2016 without the XLOOKUP function, VLOOKUP or INDEX/MATCH can be used as alternatives.

Example Use Case:

Assuming you have a table with product names in column A and their corresponding prices in column B. You can use XLOOKUP to find the price of a specific product.

=XLOOKUP("Product_ABC", A2:A100, B2:B100, "Product Not Found", 0, 1)

This formula searches for "Product_ABC" in the product names (A2:A100) and returns the corresponding price from column B. If the product is not found, it returns "Product Not Found." The match mode is set to 0 (exact match), and the search mode is set to 1 (first-to-last).