XLOOKUP

• How to use XLOOKUP formula?
• Examples of using XLOOKUP formula
• XLOOKUP formula not working?
• Similar formulas to XLOOKUP

Usage

Use the XLOOKUP formula with the syntax shown below, it has 4 required parameters and 2 optional parameters:

=XLOOKUP(search_key, lookup_range, result_range, missing_value, [match_mode], [search_mode])

1. search_key (required):
   The value or cell reference to search for in the lookup_range.
2. lookup_range (required):
   The range of cells to search for the search_key.
3. result_range (required):
   The range of cells to return a result from based on the search_key.
4. missing_value (required):
   The value to return if the search_key is not found in the lookup_range.
5. match_mode (optional):
   An optional argument that specifies how the search_key should be matched against the lookup_range. Can be set to 0 (exact match), 1 (closest match greater than or equal to), or -1 (closest match less than or equal to). Defaults to 0.
6. search_mode (optional):
   An optional argument that specifies whether the search should be done in a specific order or not. Can be set to 1 (search in descending order), or -1 (search in ascending order). Defaults to 1.

Examples

Looking up a value in a table

You can use XLOOKUP to find a specific value in a table and return a corresponding value from another column or row.

Returning a default value if no match is found

You can specify a default value to return if the search_key is not found in the lookup_range.

Matching closest value

You can use XLOOKUP to find the closest match to a given value in a range, either greater than or less than the value.

Searching in reverse order

You can use XLOOKUP to search for a value in a range in reverse order, from highest to lowest or from Z to A.

Common Mistakes

Incorrect number of arguments

Users may forget to include all required arguments or may include too many arguments, resulting in an error. Double check the syntax of the formula and ensure all arguments are included and in the correct order.

Invalid search key

If the search key is not formatted correctly or is not present in the lookup range, the formula will return an error. Ensure the search key is formatted correctly and is present in the lookup range.

Incorrect match mode

The match mode argument may be set incorrectly, resulting in unexpected results or errors. Double check the match mode argument and ensure it is set to the desired value.

Invalid result range

If the result range is not formatted correctly or is not present in the sheet, the formula will return an error. Ensure the result range is formatted correctly and is present in the sheet.

Misunderstanding of search mode

The search mode argument may be misunderstood, resulting in unexpected results or errors. Double check the search mode argument and ensure it is set to the desired value.

Related Formulas

The following functions are similar to XLOOKUP or are often used with it in a formula:

• VLOOKUP

  The VLOOKUP function is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.

• HLOOKUP

  The HLOOKUP function is a lookup formula that searches for a key in the top row of a table and returns the value in the same column for a specified row. This function is commonly used to extract data from a table based on a specific criteria.

• INDEX

  The INDEX formula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.

• MATCH

  The MATCH formula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with the INDEX formula to retrieve a value from a specific cell in a range. The search_type parameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.

Learn More

You can learn more about the XLOOKUP Google Sheets function on Google Support.