Excel XLOOKUP function

How to use the Excel XLOOKUP function

Description

The Excel XLOOKUP function searches a range or array, and returns an item corresponding to the first match it finds. XLOOKUP is a modern and powerful replacement for many older functions like VLOOKUP, HLOOKUP, or LOOKUP. It even covers the advanced functionality of INDEX MATCH formulas as it allows to lookup to the left. 

Syntax

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

Arguments

lookup_value – The value you want to search for in the first array

lookup_array – The array or range to search

return_array – The array or range to return

if_not_found – [Optional] A text string to return if no match is found

match_mode – [Optional] A value to specify the match mode to use

search_mode – [Optional] A value to specify the search mode to use

Return value

A value or row in the return_array based on the best match found

How to use the XLOOKUP function in Excel

The Excel XLOOKUP function is a modern replacement for many other functions, including the VLOOKUP function. It can find values vertically or horizontally, it is able to lookup to the left (as known from the INDEX MATCH formula), and it can return entire rows or columns, not just one value.

The syntax of XLOOKUP is much more intuitive to use than the older functions. Instead of referencing the whole table and defining a result column index, XLOOKUP allows you to define the lookup_array and the return_array separately. The function searches a given lookup_value in the lookup_array, and returns a corresponding value or row from the return_array. Here is a simple example:

= XLOOKUP(G7, C7:C15, E7:E15) 

How to use the Excel XLOOKUP function

You can see, the return_array does not need to include the lookup_array. It only has to match the return_array in its number of rows or columns. 

Lookup to the left

Thanks to this flexible setup, XLOOKUP can easily lookup values to the left by defining the lookup_array and the return_array accordingly:

Excel XLOOKUP function - Lookup to the left

This type of lookup has only been possible before with the INDEX MATCH formula. Technically, XLOOKUP can take this role from now on.

Note: As XLOOKUP is only available in Excel 365, you should consider to still use INDEX MATCH in case you share your spreadsheet with other people.

Multiple Results

Unlike the older lookup functions, XLOOKUP can return a whole row or column instead of only a single value. To return the full row in a vertical lookup, you have to reference multiple rows in the return_array argument:

= XLOOKUP(B5, B8:B14, C8:E14)    // returns 3 result values (C:E)

Excel XLOOKUP function - Multiple Return Values

Note: With this formula, 3 values are returned and spilled into the surrounding range C5:E5.

XLOOKUP Match Modes

XLOOKUP offers three modes for matching the lookup_value in the lookup_arrayYou can specify the match mode with the optional match_mode argument. Here is a list of all values you can enter for the match_mode argument:

Match Mode
Match Behaviour
0 (default)
Exact Match (will return #N/A for no match)
-1
Exact match or next smaller item
1
Exact match or next larger item
2
Wildcard match (*,?,~)

Let’s have a look at the match modes side by side:

Excel XLOOKUP function - Match Modes

The Exact Match mode works like a charm for unique lookup_values. It’s the default match mode, so you can either pass a 0 or leave the match_mode argument empty. As soon as you don’t need an exact match necessarily, the approximate match mode could be what you are looking for. In the given example, the next smallest item is selected for the ID lookup_value. In case you only know a part of the lookup_value, wildcard match is what you need to still find the closest match in the array.

XLOOKUP Search Modes

XLOOKUP has an additional feature that none of the older lookup functions have. It allows you to choose between alternative search modes. Here is a list of all available values that you can enter for the optional search_mode argument:

Search Mode
Search Behaviour
1 (default)
Search from first value
-1
Reversed search from last value
2
Binary search with values sorted in asc order
-2
Binary search with values sorted in desc order

Binary search is faster compared to normal and reversed search, but it requires you to sort the data first. For a more robust application of XMATCH, we recommend to focus on the normal and reversed order search modes (1 and -1).

Two-Way Lookup

In most use cases, you know the result_array beforehand and, thus, enter a fixed array reference. In case you want to do a two-way lookup, which means looking one value row-wise and another value column-wise, you can easily do that by nesting two XLOOKUP statements. 

In the following example, two XLOOKUP statements are nested to lookup the Store location in the first column and the Year in the first row at the same time:

= XLOOKUP(H4, B5:B11, XLOOKUP(H5, C4:E4, C5:E11))  

Excel XLOOKUP function - Two-way Lookup

With this nested XLOOKUP statement, you can dynamically lookup values based on two different lookup values.

Not Found Message

Unlike the VLOOKUP function, the XLOOKUP function provides an elegant way of handling cases in which no match is found. While for VLOOKUP you need an additional IFNA function to trap the returned #N/A error, XLOOKUP allows you to specify a text string in the optional if_not_found argument, that will be returned in case of a no match:

= XLOOKUP(H4, B5:B14, D5:D14, “Not found”)  

Excel XLOOKUP function - Not found message

You can customise this message as you like or even enter an empty string to display no message.

Formula examples

Related Tutorials

Related Functions

How to use the Excel MATCH function

Excel MATCH function

The Excel MATCH function searches for a specific item in a given range and returns the position as index number. Use MATCH and INDEX for powerful lookups.

How to use the Excel VLOOKUP function

Excel VLOOKUP function

The Excel VLOOKUP function searches for a value by matching on the first table column and returns the corresponding value from a column in the same row.

How to use the Excel FILTER function

Excel FILTER function

The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.

How to use the Excel INDEX function

Excel INDEX function

The Excel INDEX function returns the value of an element in a range or array at a given position, specified by the row and column number indexes.

Facebook
Twitter
LinkedIn
Reddit
WhatsApp
Email