Lookup & Reference
Modern
XLOOKUP
Searches a range or array and returns the matching value from a second range.
Available in Microsoft 365 · Excel 2024 · Excel 2021 · Excel for the web. Not in Excel 2016/2019 desktop.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])What it returns
The matching value (or array) from return_array.
Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value |
Yes | The value to search for. |
lookup_array |
Yes | The range or array to search in. |
return_array |
Yes | The range or array to return a value from. |
if_not_found |
No | Value to return if no match is found. |
match_mode |
No | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. |
search_mode |
No | 1 first to last (default), -1 last to first, 2 binary (ascending sort required), -2 binary (descending sort required). |
Example
Lookup value
Apple
| A | B | |
|---|---|---|
| 1 | Fruit | Price |
| 2 | Apple | 1.20 |
| 3 | Banana | 0.50 |
| 4 | Cherry | 3.00 |
Result
1.20
Find Apple in the Fruit column, return its Price.
=XLOOKUP("Apple", A2:A10, B2:B10)Important to know
Returns #N/A if no match and if_not_found is omitted.
With search_mode 2/-2 (binary), lookup_array must be sorted, or results are wrong.