Excel XLOOKUP Function Examples

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?

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

Optional arguments:

  • 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.

Syntax

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

Arguments

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.

named range

Using the “if not found” argument (4th argument of XLOOKUP)

For example, we want to find the price of the Mango, use the formula:

using xlookup if not found

=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:

Match typeAction
0 (default)Exact match (returns #N/A if no match.)
-1Returns exact match or the next smaller item.
1Gets an exact match or the next larger item.
2Wildcard match

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:

Search ModeAction
1 (default)Search from the first value
-1Search from last value (reverse)
2Binary search [values sorted in ascending order]
-2Binary 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.

prepare data using named ranges

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.

Formula:

=XLOOKUP(H3, Location, Sales)

XLOOKUP basic exact match

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).

Formula:

=XLOOKUP(H3, Sales, Location)

xlookup index match replacement left lookup

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.

Formula:

=XLOOKUP(MIN(Sales), Sales, Location)

In this case:

  • lookup value: MIN(Sales)
  • lookup array: Sales
  • return array: Location
combine xlookup with min function

Example #4 – XLOOKUP replaces INDEX + MATCH

Let us another example! We find the location where the corresponding order = 160.

Formula:

=XLOOKUP(H3, Orders, Location)

another left lookup example 4

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).

Formula:

=XLOOKUP(H3, Location, Cost,,0,-1)

not used arguments

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.

bottom to top XLOOKUP search type

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.

Formula:

=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!

XLOOKUP finds the closest value that is less than equal to the lookup 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.

Formula:

=XLOOKUP(H3, Sales, Location, ,1)

XLOOKUP finds the closest value that is greater than equal to the lookup value

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.

Formula:

=XLOOKUP(H3&”*”,Location,Orders,,2)

The most important thing: set the 5th parameter of the XLOOKUP function to 2 if you are using wildcard character search.

Result:

xlookup wildcard 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.

Formula:

=XLOOKUP(H3, Location, Sales,,2)

wildcard match example 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!

Formula:

=XLOOKUP(“*”, Location, Sales,,2,-1)

Find the cost for the last item in the list

Why XLOOKUP is better than other lookups

Key advantages of using XLOOKUP:

  1. 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.
  2. XLOOKUP handle left lookup problems and replaces the VLOOKUP + CHOOSE workaround.
  3. 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.
  4. 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.
  5. 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!

Additional resources