XLOOKUP
Description
Searches an array or range, and returns one or more matching values. XLOOKUP can do exact matching or approximate matching, vertical or horizontal lookups, and wildcard searches. This function is intended for use with unconstrained arrays. To submit the formula; use the unconstrained keyboard shortcut Ctrl+Alt+Enter (Windows) or Command+Option+Enter (Mac). For syntax details, see the Function Reference in the User Guide.
Syntax
XLOOKUP(
lookup_value
, lookup_array
, return_array
, [ if_not_found
], [ match_mode
], [ search_mode
]) - lookup_value: The lookup value.
- lookup_array: The array to search.
- return_array: The array or range to return.
- if_not_found: Text to return, or a formula to run, if the function doesn't find a valid match. If you omit this argument, the function returns an #N/A error.
- match_mode: A number that specifies the type of match.
- 0: Exact match. If not found, return #N/A. This is the default.
- -1: Exact match. If not found, return the next smaller item.
- 1: Exact match. If not found, return the next larger item.
- 2: A wildcard match. In text-based criteria, you can use the ? wildcard to match any single character or the * wildcard to match any sequence of characters. If the value to find contains a * or ?, precede it with a tilde (~).
- search_mode: A number that specifies the search mode to use.
- 1: Do a search starting with the first item. This is the default.
- -1: Do a reverse search starting with the last item.
- 2: Do a binary search that relies on lookup_array being sorted in ascending order. If it isn't sorted, the results won't be valid.
- -2: Do a binary search that relies on lookup_array being sorted in descending order. If it isn't sorted, the results won't be valid.
Notes
- XLOOKUP isn't case-sensitive by default (using a match_mode of 0). You can use XLOOKUP for a case-sensitive exact match search only if the array is sorted.
Related Functions
VLOOKUP
HLOOKUP
MHLOOKUP
MVLOOKUP