Exceljet

Quick, clean, and to the point

Excel XLOOKUP Function

Excel XLOOKUP function
Summary 

The Excel XLOOKUP function performs lookups in vertical or horizontal ranges. It is a more robust and flexible successor to older functions like VLOOKUP, HLOOKUP, and LOOKUP.  XLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. 

Purpose 
Find values in range or table
Return value 
Best match (exact or approximate)
Syntax 
=XLOOKUP (lookup, lookup_array, return_array, [not found], [match_mode], [search_mode])
Arguments 
  • lookup - The lookup value.
  • lookup_array - The array or range to search.
  • return_array - The array or range to return.
  • not found - [optional] Value to return if no match found.
  • match_mode - [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
  • search_mode - [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.
Usage notes 

The XLOOKUP function is meant to look up data in a table or list. XLOOKUP can find values in vertical or horizontal ranges, can perform approximate and exact matches, and supports wildcards (* ?) for partial matches. In addition, XLOOKUP can search data starting from the first value or the last value (see match type and search mode details below).  Compared to older functions like VLOOKUP, HLOOKUP, and LOOKUP, and XLOOKUP offers several key advantages.

Note: the [not found] argument was introduced as the 6th argument in October 2019, then moved to 4th position in early November 2019.

Not found message

When XLOOKUP can't find a match, it returns the #N/A error, like other match functions in Excel. Unlike the other match functions, XLOOKUP supports an optional argument called not_found that can be used to overide the #N/A error when it would otherwise appear. Typical values for not_found might be "Not found", "No match", "No result", etc. When providing a value for not_found, enclose the text in double quotes (""). 

Note: Be careful if you supply an empty string ("") for not_found. If no match is found, XLOOKUP will display nothing instead of #N/A. If you want to see the #N/A error when a match isn't found, omit the argument entirely. 

Match type

By default, XLOOKUP will perform an exact match. Match behavior is controlled by an optional argument called match_type, which has the following options:

Match type Behavior
0 (default) Exact match. Will return #N/A if no match.
-1 Exact match or next smaller item.
1 Exact match or next larger item.
2 Wildcard match (*, ?, ~)

Search mode

By default, XLOOKUP will start matching from the first data value. Search behavior is controlled by an optional argument called search_mode, which provides the following options:

Search mode Behavior
1 (default) Search from first value
-1 Search from last value (reverse)
2 Binary search values sorted in ascending order
-2 Binary search values sorted in descending order

Binary searches are very fast, but data must be sorted as required. If data is not sorted properly, a binary search can return invalid results that look perfectly normal.

Example #1 - basic exact match

By default, XLOOKUP will perform an exact match. In the example below, XLOOKUP is used to retrieve the Sales based on an exact match on Movie. The formula in H5 is:

=XLOOKUP(H4,B5:B9,E5:E9)

XLOOKUP - basic exact match example

More detailed explanation here.

Example #2 - basic approximate match

To enable an approximate match, provide a value for the "match_mode" argument. In the example below, XLOOKUP is used to calculate a discount based on quantity, which requires an approximate match. The formula in F5 supplies -1 for match_mode to enable approximate match with "exact match or next smallest" behavior:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

XLOOKUP - basic approximate match example

More detailed explanation here.

Example #3 - multiple values

XLOOKUP can return more than one value at the same time for the same match. The example below shows how XLOOKUP can be configured to return three matchging values with a single formula. The formula in C5 is:

=XLOOKUP(B5,B8:B15,C8:E15)

XLOOKUP - multiple value example

Notice the return array (C8:E15) includes 3 columns: First, Last, Department. All three values are returned and spill into the range C5:E5.

Example #4 - not found message

If XLOOKUP does not find a result, it returns the #N/A error. To display a custom message instead of #N/A, enclose the message in double quotes ("") as the forth argument. Based on the example above, to configure XLOOKUP to display "No result" if no matching planet is found, use:

=XLOOKUP(F4,B4:B12,D4:D12,"No result")

XLOOKUP benefits

XLOOKUP offers several important advantages, especially compared to VLOOKUP:

  • XLOOKUP can lookup data to the right or left of lookup values
  • XLOOKUP can return results from more than one column (as in the example)
  • XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate)
  • XLOOKUP can work with vertical and horizontal data
  • XLOOKUP can search in reverse order
  • XLOOKUP can return a row or column, not just one value
  • XLOOKUP can work with arrays natively

Notes

  1. XLOOKUP can work with both vertical and horizontal arrays.
  2. XLOOKUP will return #N/A if the lookup value is not found
  3. The lookup_array must have a dimension compatible with the return_array argument, otherwise XLOOKUP will return #VALUE!
  4. If XLOOKUP is used between workbooks, both workbooks must be open, otherwise XLOOKUP will return #REF!.
XLOOKUP is a beta function, available only through the Office Insiders program.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.