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)
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:
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)
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_array. You 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:
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:
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”)
You can customise this message as you like or even enter an empty string to display no message.
Formula examples
Related Tutorials
Related Functions
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.
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.
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.
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.
