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 
Lookup values in range or table
Return value 
Result as array
Syntax 
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
Arguments 
  • lookup_value - The lookup value.
  • lookup_array - The array or range to search.
  • return_array - The array or range to return.
  • match_mode - [optional] 1 = 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 Excel XLOOKUP function is a lookup function to retrieve data from a table of information. XLOOKUP can perform lookups in vertical or horizontal ranges, and is meant to be a more flexible and powerful successor to older functions like VLOOKUP, HLOOKUP, and LOOKUP.    XLOOKUP supports both approximate and exact matches, and wildcards (* ?) for partial matches. In addition, XLOOKUP can search data starting from the first value or the last value (reverse lookup). See match type and search mode details below.

Advantages over VLOOKUP

XLOOKUP offers several important advantages over 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 returns a range instead of just a value

Match type

The forth argument for XLOOKUP is match_type. This is an optional argument that controls match behavior as follows:

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

The fifth argument for XLOOKUP is search_mode. This is an optional argument that controls search behavior as follows:

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 take care data is sorted as required. If data is not sorted properly, a binary search can return invalid results that look perfectly normal.

Example #1 - multiple results

In the example shown, XLOOKUP is used to retrieve employee information based on an ID entered in B6. The formula in C6 is:

=XLOOKUP(B6,B9:B16,C9:F16) // all columns

Notice the result_array is  C9:F16 , so all data in columns C through F is returned with one formula entered in C6. The data "spills" into the range C6:F6 automatically.

Example #2 - single result

To return just one value, adjust the return array as needed:

=XLOOKUP(B6,B9:B16,C9:C16) // first name
=XLOOKUP(B6,B9:B16,D9:D16) // last name

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!.
This 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.