The Excel XLOOKUP function replaces the older lookup functions like VLOOKUP, HLOOKUP and supports lookups for horizontal a vertical ranges.
Table of contents:
- What is XLOOKUP
- How can I get XLOOKUP
- How to use Excel XLOOKUP function
- Syntax, Arguments
- XLOOKUP Function examples
- Why XLOOKUP is better than other lookups
What is XLOOKUP?
XLOOKUP is the latest lookup function in Excel and sends a “Happy Retirement” message to the older lookup functions, like VLOOKUP, HLOOKUP, LOOKUP, and the INDEX+MATCH combination.
XLOOKUP is similar to VLOOKUP but uses six arguments. Three arguments are required, another three are optional.
- The value you are looking for: lookup_value
- The list where we find the lookup value: lookup_array
- The list from which you want the result: return_array
- Error handling; what if the value is not found: not_found
- Declare match types: match_mode
- Controls the search action: search_mode
How can I get XLOOKUP
XLOOKUP is only available for the Microsoft365 (formerly Office365) users. If you are using an older version of Microsoft Excel (2010, 2013, 2016, 2019), you cannot use the function.
That was the official information, but it is tricky to use it with any Excel versions.
If you want to use XLOOKUP with the recent Excel versions, check our add-in that provides the latest lookup functionality for all Excel versions.
How to use XLOOKUP Function
XLOOKUP lookup values in a range or array and returns matching value(s) from the return array.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Here is some basic example.
Basic usage (using three arguments)
=XLOOKUP(what you want to find, lookup list, result list)
=XLOOKUP(“Banana”, B3:B6, D3:D6) returns Banana if the name can be found in column B.
For the sake of simplicity, use named ranges and add “Fruits” to range B3:B6 and “Price” to range D3:D6.
Using the “if not found” argument (4th argument of XLOOKUP)
For example, we want to find the price of the Mango, use the formula:
=XLOOKUP(Mango”, Fruits, Price, ”Not found”) return “Not found” if the lookup value (Mango) is not available in column B.
Match mode or type (5th argument)
Use match mode as a 5th argument of XLOOKUP to declare Excel how you want your MATCH to happen. These are the following:
|0 (default)||Exact match (returns #N/A if no match.)|
|-1||Returns exact match or the next smaller item.|
|1||Gets an exact match or the next larger item.|
Search mode (6th argument)
Good to know that XLOOKUP will start matching from the first data value by default. The search_mode optional argument controls the search action and uses the following options:
|1 (default)||Search from the first value|
|-1||Search from last value (reverse)|
|2||Binary search [values sorted in ascending order]|
|-2||Binary search [values sorted in descending order]|
XLOOKUP Function Examples
If your Excel version supports XLOOKUP, download the sample workbook and play with it.
Use names for a range to make your learning curve fast and easier. For example, XLOOKUP works fine if you use classic cell references, but structured references provide easy-to-understand formulas.
Before we take a deep dive into XLOOKUP, prepare your table, it can take seconds. First, select the range you want to use and add a name.
Next, locate the name box, enter a descriptive name, and press Enter.
We’ll use the following named ranges:
- Location (range B3:B13)
- Orders (range C3:C13)
- Sales (range D3:D13)
- Cost (range E3:E13)
Example #1 – Basic Exact Match
In the example, we’ll use only the first three arguments of the XLOOKUP function. First, you want to know the sales for Delaware.
By default, XLOOKUP uses exact match. Therefore, in the example below, XLOOKUP is used to retrieve sales (from column D) based on an exact match on location.
=XLOOKUP(H3, Location, Sales)
Example #2 – Lookup right to left
In the example, we’ll show you how to replace the old INDEX + MATCH combination if you need to use left lookup.
Now we find the location where the sales are 9460. In this case, the lookup array is D3:D13 (Sales), the return array is B3:B13 (Location).
=XLOOKUP(H3, Sales, Location)
Example #3 – Combine XLOOKUP with the MIN function
In the example, we’ll combine the XLOOKUP function with the MIN function to get the minimum sales and the correspondent location.
=XLOOKUP(MIN(Sales), Sales, Location)
In this case:
- lookup value: MIN(Sales)
- lookup array: Sales
- return array: Location
Example #4 – XLOOKUP replaces INDEX + MATCH
Let us another example! We find the location where the corresponding order = 160.
=XLOOKUP(H3, Orders, Location)
Example #5 – Specify match type and search order
In the example, we’ll use two optional arguments for XLOOKUP to specify match type (0 is for exact match) and match direction (-1 is for the bottom to top).
=XLOOKUP(H3, Location, Cost,,0,-1)
We use two (5th – match mode and 6th – search mode) of the three optional arguments. Parameters are comma-separated-values, so if the 4th parameter is not necessary, type a comma, then add the 5th and 6th argument to XLOOKUP.
Example #6 – Find the closest value (next smaller item)
We’ll demonstrate how to find the closest value. First, the XLOOKUP function searches for an exact match. If not found, XLOOKUP returns the next smaller item if you use the -1 parameter as a match mode.
=XLOOKUP(H3, Sales, Location,,-1)
Take a closer look at the result! For example, in cell D9, the value is 10252, less than the lookup value. But it’s the closest value!
Example #7 – Find the closest value (next larger item)
Let us find the closest value but change the 5th parameter to 1. In this case, the XLOOKUP function searches for an exact match, but if the exact match is not found, XLOOKUP returns the next larger item.
Change the match mode to = 1.
=XLOOKUP(H3, Sales, Location, ,1)
Example #8 – Wildcard character match (*)
We’ll show you how to use the XLOOKUP wildcard search function in the following example.
Okay, look up the location that begins with “Ne” and returns the orders. Use “*” for any number of letters.
The most important thing: set the 5th parameter of the XLOOKUP function to 2 if you are using wildcard character search.
Example #8 – Wildcard character match (?)
In the example, we’ll use the “?” symbol to replace a single letter in a word. Don’t forget that we need to use 2 as a parameter for match mode in this case.
=XLOOKUP(H3, Location, Sales,,2)
Example #9 – Search last to first
Finally, let us see how to use the 6th argument of the XLOOKUP function. Find the cost for the last item on the list!
=XLOOKUP(“*”, Location, Sales,,2,-1)
Why XLOOKUP is better than other lookups
Key advantages of using XLOOKUP:
- XLOOKUP reduces formula errors: Type XLOOKUP(value_to_find, where_to_find, the result list) and job done. If the value is not found, the formula returns with #N/A.
- XLOOKUP handle left lookup problems and replaces the VLOOKUP + CHOOSE workaround.
- Returns exact match by default. The most frustrating thing about using HLOOKUP and VLOOKUP is the last parameter. You have to use FALSE to get the correct result. On the other hand, XLOOKUP returns with the exact match by default.
- XLOOKIP supports a 4th parameter: the “value not found” scenario. From now, error handling is much better. Instead of using IFERROR or IFNA functions, you can add the default output if the value is not found.
- XLOOKUP uses an optional 6th parameter for special searches and supports wildcard, top, and bottom searches.
Thanks for being us today! Stay tuned and follow our latest function guides and tutorials!