Excel XLOOKUP Function

The Excel XLOOKUP function is a modern and adaptable trade for older functions like VLOOKUP, HLOOKUP, and LOOKUP. The XLOOKUP function in Microsoft Excel permits us to search entries in a huge exhibit of information. 

The Syntax is:

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

where Arguments are as follows:

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 such match found.

match_mode - [optional] 0 = identical match (default), -1 = identical match or next smallest, 1 = identical match or next larger, 2 = wildcard match.

search_mode - [optional] 1 = find from first (default), -1 = find from last, 2 = binary search ascending, -2 = binary search descending.

XLOOKUP is the most up to date individual from the Excel lookup function family. You may definitely know its kin – VLOOKUP, HLOOKUP, INDEX+MATCH, LOOKUP and so on. Microsoft presented the XLOOKUP function in a 2019 update. The VLOOKUP  is one of the most mainstream Excel functions and is generally utilized in budgetary display. XLOOKUP is intended to address a portion of VLOOKUP's impediments, for example, the inability to search to the left of the lookup array.

XLOOKUP permits us to look for an item in a range (or table) and return matching outcomes. As it were, it is like VLOOKUP, however it offers quite a lot more. 

When utilizing XLOOKUP, you simply give 3 fundamental parameters (and 3 more optional parameters) and Excel wraps up as explained above under syntax and arguments. 

• The value you are searching for 

• The list where this value ought to be found 

• The list from which you need the outcome 

• [Optional] value if not found 

This function is at present accessible to Office 365 endorsers in the Monthly channel. It will be accessible to Office 365 supporters in the Semi-Annual divert beginning in July 2020. 

1. Simple XLOOKUP Formula

In its most basic form, XLOOKUP looks through a range of cells and returns an item corresponding to the first match it finds.

2. XLOOKUP Formula Returns Multiple Columns

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

3. XLOOKUP Dynamic Range

Since we know XLOOKUP can restore a range, we can utilize it to restore a unique range, which you can name. No more requirements for OFFSET or INDEX and MATCH to make dynamic named ranges. In the example below, the sum of sales value is calculated from the beginning date (G3) to the end date (H3). 

Note: dates are dd/mm/yyyy.

4. XLOOKUP Function Error Handling

Back in the early days of VLOOKUP we used IF(ISNA(VLOOKUP… to deal with errors. At that point came IFERROR which made error handling simpler and increasingly effective for Excel. But with XLOOKUP we don’t need any extra functions to deal with errors because the fourth argument, if_not_found, permits us to determine a value to be returned in the event XLOOKUP doesn’t find a match.

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

5. XLOOKUP Last Value

By default, XLOOKUP looks through first to last, which is search_mode 1. Utilizing - 1 in the search_mode argument advises XLOOKUP to look from the bottom up, subsequently finding the last matching value. The picture below shows XLOOKUP restoring the last Sales value for Clothing:

We can also perform binary searches where our lists are sorted with 2 or -2:

The XLOOKUP function is the eagerly awaited successor to both the HLOOKUP and VLOOKUP functions.

A variety of examples were used in this article to show the advantages of XLOOKUP. One of which is that XLOOKUP can be utilized across sheets, exercise manuals and furthermore with tables. The examples were kept simple in the article to help understand better.

The days of VLOOKUP are numbered. XLOOKUP is here and will soon be the de facto lookup equation.