How to use XLOOKUP Function in Excel

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

Syntax:= XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

The XLOOKUP function syntax has the following 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.

Example: Let’s look at some Excel XLOOKUP function examples and explore how to use the XLOOKUP function as a worksheet function in Microsoft Excel:

1. Simple XLOOKUP Formula
In its most basic form XLOOKUP searches a range of cells and returns an item corresponding to the first match it finds.

Syntax: =XLOOKUP(G1;B2:B17;E2:E17)

Result:

The lookup_array doesn’t need to be sorted because XLOOKUP will return an exact match by default.

In English the formula above reads, lookup the value in cell G1, which is Ben Alexander, in cells B2:B17 and return the value on the corresponding row in cells E2:E17. If you don’t find an exact match, return an error. This last part is the default behaviour because I didn’t provide a value in the if_not_found argument.

2. XLOOKUP Function does HLOOKUP
Not only does XLOOKUP replace VLOOKUP, but it can also perform HLOOKUPs:

Syntax: =XLOOKUP(A4;$A$1:$H$1;$A$2:$H$2)

Result:

Based on the Excel spreadsheet above, the following XLOOKUP examples would return:

3. XLOOKUP Formula Returns Multiple Columns

In the formula below the return_array argument is columns C, D and E. With Office 365 XLOOKUP will return multiple values as dynamic arrays allow XLOOKUP to spill the results.

Syntax: =XLOOKUP(B20;B2:B17;C2:E17)

Result:

4. XLOOKUP Dynamic Range
Now that we know XLOOKUP can return a range, we can use it to return a dynamic range, which you can name. No more need for OFFSET or INDEX & MATCH to create dynamic named ranges.

In the example below I want to sum the sales values from the start date (A20) to the end date (B20).

Note: My dates are dd/mm/yyyy.

Syntax: =SUM(XLOOKUP(A20;C2:C17;E2:E17):XLOOKUP(B20;C2:C17;E2:E17))

Result:

5. XLOOKUP Function Error Handling
Back in the early days of VLOOKUP we used IF(ISNA(VLOOKUP… to handle errors. Then came IFERROR which made life simpler and more efficient for Excel. But with XLOOKUP we don’t need any extra functions to handle errors because the fourth argument, if_not_found, allows us to specify a value to be returned in the event XLOOKUP doesn’t find a match.

In the example below I’ve entered the text ‘Not Found’ in the if_not_found argument. Alternatively you can enter numbers, another formula, an array or cell reference.

Syntax: =XLOOKUP(G1;B2:B17;E2:E17;”It not found”)

Result:

Note: If you omit the if_not_found argument and a match cannot be found, XLOOKUP will return #N/A.

6. XLOOKUP Last Value

By default, XLOOKUP searches first to last, which is search_mode 1. Using -1 in the search_mode argument tells XLOOKUP to search from the bottom up, thus finding the last matching value. The image below shows XLOOKUP returning the last Sales value for Clothing:

Syntax: =XLOOKUP(G1;A2:A17;E2:E17;;0;-1)

Result:

In earlier versions of Excel, binary searches evaluated more quickly, but according to Microsoft in Office 365 this is no longer the case. As a result, there is no significant benefit to using the binary search options and in fact it’s easier to use 1 or -1 search modes because they don’t require the table to be sorted.

7. XLOOKUP Left
One of the limitations of VLOOKUP is the inability to return values to the left of the lookup column. XLOOKUP isn’t hindered by that limitation, as you can see below:

Syntax: =XLOOKUP(A2;$H$2:$H$17;$G$2:$G$17)

Result:

Based on the Excel spreadsheet above, the following XLOOKUP examples would return:

If you don’t have the XLOOKUP function you can use INDEX & MATCH or the workaround with CHOOSE to trick VLOOKUP into looking up to the left.

8. XLOOKUP Function Wildcards
VLOOKUP supports wildcards for partial matches by default, which meant looking up words that contained a wildcard like an asterisk e.g. *Stella, would require the wildcard character to be prefixed by the tilde e.g.:

=VLOOKUP( “~*Stella”, B2:C15, 2, 0)
XLOOKUP only supports wildcards if you specify 2 in the match_mode argument and therefore you don’t need to prefix the wildcard with the tilde:

Syntax: =XLOOKUP(G1;B2:B17;E2:E17;;2)

Result:

Note: Wildcards cannot be used in binary search mode.

9. XLOOKUP Function Approximate Match
We can use the match_mode argument to return an approximate match. The formula in the image below uses -1 to find an exact match or the next smallest value in the lookup_range (F2:F6).

Syntax: =XLOOKUP(C2;$F$2:$F$6;$G$2:$G$6;;-1)

Result:

Similarly, specifying 1 in the match_mode argument will return and exact match or the next largest item.

Tip: What’s even better is that the lookup_range doesn’t need to be sorted.

Note:

XLOOKUP is a modern replacement for the VLOOKUP function. It is a flexible and versatile function that can be used in a wide variety of situations.

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, XLOOKUP offers several key advantages.

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:

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:

XLOOKUP can work with both vertical and horizontal arrays.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.

1. XLOOKUP will return #N/A if the lookup value is not found.
2. The lookup_array must have a dimension compatible with the return_array argument, otherwise XLOOKUP will return #VALUE!
3. If XLOOKUP is used between workbooks, both workbooks must be open, otherwise XLOOKUP will return #REF!.
4. Like the INDEX function, XLOOKUP returns a reference as a result.