XLOOKUP searches a range or array, and returns the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.Â
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Â
In plain language, the XLOOKUP function says =XLOOKUP( what to look up, where to look for it, the array or range to return, a value to return in not found. the kind of match, and the search mode to use).
|
Argument |
Description |
|---|---|
|
lookup_value Required* |
The value to search for *If omitted, XLOOKUP returns blank cells it finds in lookup_array.  |
|
lookup_array Required |
The array or range to search |
|
return_array Required |
The array or range to return |
|
[if_not_found] Optional |
Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned. |
|
[match_mode] Optional |
Specify the match type: 0 - Exact match. If none found, return #N/A. This is the default. -1 - Exact match. If none found, return the next smaller item. 1 - Exact match. If none found, return the next larger item. 2 - A wildcard match where *, ?, and ~ have special meaning. |
|
[search_mode] Optional |
Specify the search mode to use: 1 - Perform a search starting at the first item. This is the default. -1 - Perform a reverse search starting at the last item. 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
For more info, see XLOOKUP function.
XLOOKUP searches a range or array, and returns the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.Â
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Â
In plain language, the XLOOKUP function says =XLOOKUP( what to look up, where to look for it, the array or range to return, a value to return in not found. the kind of match, and the search mode to use).
|
Argument |
Description |
|---|---|
|
lookup_value Required* |
The value to search for *If omitted, XLOOKUP returns blank cells it finds in lookup_array.  |
|
lookup_array Required |
The array or range to search |
|
return_array Required |
The array or range to return |
|
[if_not_found] Optional |
Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned. |
|
[match_mode] Optional |
Specify the match type: 0 - Exact match. If none found, return #N/A. This is the default. -1 - Exact match. If none found, return the next smaller item. 1 - Exact match. If none found, return the next larger item. 2 - A wildcard match where *, ?, and ~ have special meaning. |
|
[search_mode] Optional |
Specify the search mode to use: 1 - Perform a search starting at the first item. This is the default. -1 - Perform a reverse search starting at the last item. 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
For more info, see XLOOKUP function.
XLOOKUP searches a range or array, and returns the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.Â
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Â
In plain language, the XLOOKUP function says =XLOOKUP( what to look up, where to look for it, the array or range to return, a value to return in not found. the kind of match, and the search mode to use).
|
Argument |
Description |
|---|---|
|
lookup_value Required* |
The value to search for *If omitted, XLOOKUP returns blank cells it finds in lookup_array.  |
|
lookup_array Required |
The array or range to search |
|
return_array Required |
The array or range to return |
|
[if_not_found] Optional |
Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned. |
|
[match_mode] Optional |
Specify the match type: 0 - Exact match. If none found, return #N/A. This is the default. -1 - Exact match. If none found, return the next smaller item. 1 - Exact match. If none found, return the next larger item. 2 - A wildcard match where *, ?, and ~ have special meaning. |
|
[search_mode] Optional |
Specify the search mode to use: 1 - Perform a search starting at the first item. This is the default. -1 - Perform a reverse search starting at the last item. 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
For more info, see XLOOKUP function.
