XLOOKUP Function

The XLOOKUP function is one of the lookup and reference functions. It is used to perform the search for a specific item by row both horizontally and vertically. The result is returned in another column and can accommodate two-dimensional datasets.

The XLOOKUP function syntax is:

XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

where

lookup_value is a value to search for.

lookup_array is an array or range to search in.

return_array is an array or range to return the results to.

if_not_found is an optional argument. If there is no search result, the argument returns the text stated in [if_not_found]. In case the text is not specified, the “N/A” is returned.

match_mode is an optional argument. The following values are available:

• 0 (set by default) returns the exact match; if there is no match, the “N/A” is returned instead.
• -1 returns the exact match; if there is none, the next smaller item is returned.
• 1 returns the exact match; if there is none, the next larger item is returned.
• 2 is a wildcard match.

search_mode is an optional argument. The following values are available:

• 1 starts a search at the first item (set by default).
• -1 starts a reverse search, i.e. at the last item.
• 2 starts a binary search with the lookup_array sorted in ascending order. If not sorted, invalid results will be returned.
• -2 starts a binary search with the lookup_array sorted in descending order. If not sorted, invalid results will be returned.

Wildcard characters include the question mark (?) that matches a single character and the asterisk (*) that matches multiple characters. If you want to find a question mark or asterisk, type a tilde (~) before the character.

To apply the XLOOKUP function,

1. select the cell where you wish to display the result,
2. click the Insert function
   icon situated at the top toolbar,
   or right-click within a selected cell and select the Insert Function option from the menu,
   or click the
   icon situated at the formula bar,
3. select the Lookup and Reference function group from the list,
4. click the XLOOKUP function,
5. enter the required arguments in the Function Arguments window,
6. press the Enter button.

The result will be displayed in the selected cell.

Return to previous page