Description
Use this function to search a range or an array, and return an item corresponding to the first match it finds.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
lookup_value |
Yes | The value to search for. | A cell reference, a cell range, a number, a text string, or a formula which results in any of these. |
lookup_array |
Yes | The range or array to search within. | A cell range or a formula which results in a cell range. |
return_array |
Yes | The range or array to return the value from. | A cell range or a formula which results in a cell range. |
if_not_found |
No | The value to return if no match is found. | A cell reference, a cell range, a number, a text string, or a formula which results in any of these. |
match_mode |
No | How the match is to be made. |
0 for exact match (default), -1 for exact match or next smaller item, 1 for exact match or next larger item, 2 for wildcard match. |
search_mode |
No | The search mode and order. |
1 to search from first to last (default), -1 to search from last to first, 2 to search a binary search in ascending order, -2 to search a binary search in descending order. |
Example
Sample data
| A | B | |
|---|---|---|
| 1 | Apple | $1.25 |
| 2 | Banana | $0.75 |
| 3 | Cherry | $2.50 |
| 4 | Date | $3.00 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Look up a value in a column and return a corresponding value from another column. | =XLOOKUP("Banana", A2:A5, B2:B5) |
This formula works in the following manner:
For this data set, this formula returns $0.75. |
| Provide a custom value if the lookup value isn't found. | =XLOOKUP("Cherry", A2:A5, B2:B5, "Not Found") |
This formula works in the following manner:
For this data set, this formula returns $2.50. |
| Provide a custom value if the lookup value isn't found. | =XLOOKUP("Grape", A2:A5, B2:B5, "Not Found") |
This formula works in the following manner:
For this data set, this formula returns Not Found. |
| Search for a value starting from the last cell in a range. | =XLOOKUP("Date", A2:A5, B2:B5, , , -1) |
This formula works in the following manner:
For this data set, this formula returns $3.00. |
Notes
- XLOOKUP can search both vertically and horizontally.
- If there is no match and no
if_not_foundvalue is specified, XLOOKUP returns a #N/A error. - XLOOKUP can replace older functions such as VLOOKUP and HLOOKUP.
- Wildcards such as * and ? can be used in
lookup_valuewhenmatch_modeis set to 2.
Tips
- Use XLOOKUP to simplify your lookup formulas, as it offers more functionality and flexibility than older lookup functions.
- Consider using the
if_not_foundparameter to handle missing data gracefully. - Use XLOOKUP in combination with other functions to create more complex formulas and data retrieval methods.