XLOOKUP function is a lookup function that searches for a given value in a given range and returns a corresponding value.
XLOOKUP is only available for Office 365 subscribers from July 2020. If you have an older version of Microsoft Excel then refer to VLOOKUP and INDEX-MATCH functions.
XLOOKUP has 3 required and 3 optional arguments:
lookup_value:
Is a value which will be searched. In the example above we are attempting to look up sales for Region 4. Select the cell where value resides. The value can be hard written into the formula as 4.
lookup_array:
Is a row or column where look_up value resides.
return_array:
Is a row of column that contains values which will be returned when look_up value is found in the lookup_array.
[if_not_found]:
Type a value or a text string surrounded by quotations "Example" to return if lookup_value is not found. By default Excel will return N/A.
[match_mode]:
Set to exact match by default. Other options are available to return to next smallest or largest value if looking up dates or numbers. Essentially, this is an IF statement and an alternative to [if_not_found] statement. The logic reads: "If error then go to next higher(lower) value".
[search_mode]:
Allows for flexibility where lookup_array contains non unique value. By default, all lookup functions in Excel run from top row to bottom row. This argument allows for some flexibility such as running a lookup from bottom to the top.