XLOOKUPformula is used to search for a specific value in a range and return a corresponding result from another range. This formula is a more powerful version of the
HLOOKUPformulas as it allows for more flexible searching and matching options.
- How to use
- Examples of using
XLOOKUPformula not working?
- Similar formulas to
XLOOKUP formula with the syntax shown below, it has 4 required parameters and 2 optional parameters:
- search_key (required):
The value or cell reference to search for in the lookup_range.
- lookup_range (required):
The range of cells to search for the search_key.
- result_range (required):
The range of cells to return a result from based on the search_key.
- missing_value (required):
The value to return if the search_key is not found in the lookup_range.
- 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.
- 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.
ExamplesHere are a few example use cases that explain how to use the
XLOOKUPformula in Google Sheets.
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.
XLOOKUPnot working? Here are some common mistakes people make when using the
XLOOKUPGoogle Sheets Formula:
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.
The following functions are similar to
XLOOKUP or are often used with it in a formula:
VLOOKUPfunction 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.
HLOOKUPfunction 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.
INDEXformula 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.
MATCHformula 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
INDEXformula to retrieve a value from a specific cell in a range. The
search_typeparameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.
You can learn more about the
XLOOKUP Google Sheets function on Google Support.