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 (on Windows) or Command+Option+Enter (on
Mac). For syntax details, see the Function Reference in the User Guide (Help > 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.
- search_mode: A number that specifies the search mode to use.
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