Excel XLOOKUP Function Examples

XLOOKUP finds the lookup value in a vertical or horizontal array and supports exact match, wildcards, and binary search.

Table of contents:

What is XLOOKUP?

XLOOKUP is a game-changer, a powerful and versatile lookup function in Excel. It provides flexible solutions to replace the older lookup functions, like VLOOKUP, HLOOKUP, LOOKUP, and the INDEX+MATCH combination.

How can I get XLOOKUP

XLOOKUP is only available for the Microsoft365 (formerly Office365) users. In addition, you cannot use the function if you use an older version of Microsoft Excel (2010, 2013, 2016, 2019). That was the official information, but using it with any Excel version is tricky.

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

This section will explain how the XLOOKUP syntax and arguments work.

Syntax, Arguments

XLOOKUP uses the following syntax:

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

XLOOKUP is similar to VLOOKUP but uses six arguments. Three arguments are required, and another three are optional.

Required arguments:

  • 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

Before we start using XLOOKUP, it is worth creating named ranges! The process can take seconds, and we will get an easy-to-understand structure for your data. To create a named range, select the range you want to name, locate the name box and type a name – finally, press Enter.

XLOOKUP named range

We will use four named ranges:

  • Location = B3: B11
  • Orders = C3: C11
  • Sales = D3: D11
  • Cost = E3: E11

XLOOKUP works fine using classic cell references, but structured references provide easy-to-understand formulas.

Basic usage, exact match (required arguments only)

In the example below, we want to find the Sales for New York. So, we use XLOOKUP to return the “Sales” based on an exact match in the list, “Location”.

Good to know that by default, the function returns an exact match; no additional argument is necessary.

basic usage

General formula:

=XLOOKUP(what we want to find, lookup list, result list)

=XLOOKUP(G3, Location, Sales)

Explanation: The formula finds the lookup value in the lookup array and returns $5178 if the lookup value is available in column B. For simplicity, we use the above-mentioned named ranges.

XLOOKUP “if not found” argument (4th argument)

XLOOKUP returns the #N/A error when not finding a match in a return array and all other Excel lookup functions get the same error message.

The function uses an optional argument that supports error handling in place without using additional functions. You can choose two practical ways to create a more user-friendly output in case of no match. First, apply double quotes (“”) to build an empty string result.

Furthermore, you can customize the message when the formula does not find a matching value. For example, choose an easy-to-understand string instead of #N/A, like: “not found”, “no result”, or “value not found”.

XLOOKUP error handling using the if not found argument

In the example, find the sales where the location is Texas. What if the lookup array does not contain the lookup value? Without using the 4th argument, the result is an #N/A error. Use the optional 4th argument to create a string instead of the #N/A error.

Error handling formula using XLOOKUP:

=XLOOKUP(G3, Location, Sales, “Not found”)

Explanation: The formula returns the “Not found” string if the lookup value (Texas) is unavailable in column B.

Match mode or type (5th argument)

Use match mode as a 5th argument of XLOOKUP to declare in Excel how you want your MATCH to happen. These are the following:

Match typeAction
0 (default)Exact match (returns #N/A if no match.)
-1Exact match or the next smaller item.
1Exact 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; the array contains unsorted values.
-1search from the last value in an array (reverse search)
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. The practice Workbook contains all questions and answers using the proper formula. Use names for a range to make your learning curve fast and more straightforward.

Download the practice file.

XLOOKUP Lookup right to left

XLOOKUP has native left lookup support. Therefore, you can use it instead of VLOOKUP and CHOOSE-based formula.

In the example, we want to find the location in column B, where the cost = $498. Therefore, the lookup array is E3:E13 (Cost), and the return array is B3:B13 (Location).

XLOOKUP left lookup

Use the following formula to lookup right to left:

=XLOOKUP(G3, Cost, Location)

If your Excel version does not support XLOOKUP, here is a right-to-left workaround with the VLOOKUP and CHOOSE functions. First, use the CHOOSE function to restructure the lookup table and replace the Sales and Location columns.

=CHOOSE({1,2}, Cost, Location)

VLOOKUP will perform the usual left-to-right lookup and return with the matching location based on the lookup value.

Formula:

=VLOOKUP(G3,CHOOSE({1,2},Cost,Location), 2, 0)

Combine XLOOKUP with the MIN or MAX functions

You can easily combine XLOOKUP with Excel functions, like MIN or MAX. In the example, we will use XLOOKUP with the MIN function to return the minimum sales with the corresponding location.

Formula:

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

Use the following arguments:

  • lookup value: MIN(Sales)
  • lookup array: Sales
  • return array: Location
combine XLOOKUP with other Excel functions

Explanation: The =MIN(Sales) expression returns with the minimum value in the Sales array. Therefore the lookup value is based on the result of the MIN function. Finally, XLOOKUP will use the result as a lookup value.

Replace INDEX and MATCH functions

One of the benefits of using XLOOKUP is that it completely replaces the INDEX and MATCH-based formulas. In the example, we aim to find the ‘Orders’ where the ‘Sales’ = $2486.

Configure XLOOKUP arguments the following:

  • lookup value = 2486
  • lookup array = Sales
  • return array = Orders
INDEX mATCH replacement

Formula:

=XLOOKUP(2486, Sales, Orders)

The result is Oregon because here, we have 160 orders.

Okay, that was easy! Let us see what will happen if we don’t have XLOOKUP. Now we explain the possible workaround with the INDEX and MATCH combinations.

INDEX and MATCH formula:

=INDEX(Orders, MATCH(2486, Sales, 0))

Evaluate the formula working from the inside out. Next, the MATCH function finds the lookup value (2486) in the Sales column and returns with row number 8. Finally, INDEX will use the row number as a second argument.

You can easily decide which formula is shorter, better, and flexible.

XLOOKUP Match mode: Find the next smaller item (closest value)

It’s time to take a closer look at the XLOOKUP match types. Until now, we used exact match (default value = 0) as a match_mode. The following example will demonstrate the power of a match mode. The fifth argument of the function is a swiss-knife. We will show you why.

With XLOOKUP, you can use the -1 parameter as a match mode to find the next smaller item, the closest value.

In the example, our lookup value is $5200 in the sales column, and try to find the location where the sales are less than or equal to $5200.

Formula:

=XLOOKUP(G3, Sales, Location,,-1)

XLOOKUP Find the next smaller item

The result is New York.

Explanation: First, the formula tries to search for an exact match. There is no exact match; XLOOKUP returns the next smaller item. In cell D8, the sales = $5178, which is less than the lookup value and meets the criteria, so it is the closest value!

XLOOKUP Match mode: Find the next larger item (closest value)

Let us see what will happen if we set the match mode to 1. In the example, we want to find the closest value greater than or equal to the lookup value.

To find the next larger item (closest value), change the 5th parameter to 1. The lookup value is in the Sales column, 9500.

Find the next larger item

XLOOKUP does not find an exact match, so that it will use the next larger item as a lookup value, in this case, 9740.

Formula:

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

Result: the corresponding item based on the lookup value: Illinois.

If you want to learn more about this feature, here is a more advanced example of how to find the closest match in an array.

Wildcard character match (* or ?)

We’ll show you how to use the XLOOKUP wildcard search function in the following example.

We aim to look up the location that begins with “DE” and return the sales. Use “*” for any number of letters. Set the 5th parameter of the XLOOKUP function to 2 if you are using wildcard character search.

  • lookup value: = G3&”” = De*
  • lookup array = Location
  • return array = Sales
  • match mode = 2

Formula:

=XLOOKUP(G3&”*”, Location,Sales,,2)

wildcard search function example

Sometimes you want to replace a single letter in a word, use the “?” symbol. We use the wildcard search, so don’t forget to use 2 as a parameter for match mode.

XLOOKUP wildcard character search single word

Formula:

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

Learn more about wildcards, and here is a detailed example of how to return the last match in a range in case of an exact match.

XLOOKUP last to first search mode

The following two examples will explain how to use the search_mode argument of the XLOOKUP function.

In the first 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).

We aim to find the sales for “Arizona” using the last to first search mode.

Formula:

=XLOOKUP(“Arizona”, 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 arguments to XLOOKUP.

last to first search

Reverse search starting with the last item. The result is 771 because we are using the bottom-to-top search.

XLOOKUP first to last search mode

Now, let us see what will happen if we use the bottom-to-top search. Our goal is to same as the example mentioned above: find the matching record in the “Cost” column where the location is equal to “Arizona”.

Formula:

=XLOOKUP(“Arizona”, Location, Cost,,0,1)

In this case, we use “1” as the 6th argument.

first to last search mode

Explanation: The formula searches for the first matching value in the range ‘Cost’. The result is 575 (not 771) because we use the first to last search mode.

A binary search can be faster than a linear search if you work with a huge array and your data is sorted. Let us see the workaround with binary search lookups. In the picture below, we try to find the lookup value in an unsorted array using binary search.

In the example, we want to find the matching value in a Sales column for the lookup value, “Oregon”. First, make sure that the search_mode argument = 2.

Evaluate the formula:

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

binary search lookup error

The result is an #N/A error. To avoid this type of error, ensure your array is sorted. In this example, you must apply a sort to get the Sales column items in ascending order.

Learn more about binary search.

XLOOKUP multiple results

What if we have multiple matching records? Is it possible to get all matching records using XLOOKUP?

In the example, we want to list all sales records where the location is Arizona. Therefore, use the FILTER function instead of XLOOKUP.

Then, enter the formula in cell G6:

=FILTER(B3:D11, Location=G3)

Excel automatically spills the results into adjacent blank cells.

get all matching records filter

Why XLOOKUP is better than other lookups (VLOOKUP, HLOOKUP, INDEX, and MATCH)

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 handles 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. The function is not case-sensitive by default. Here is the workaround to get case-sensitive values.
  5. XLOOKUP 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.
  6. It uses an optional 6th parameter for special searches and supports wildcard, top, and bottom searches.
  7. Look up values using multiple criteria.
  8. It works great if you are using logical criteria as a lookup array.
  9. Spill the matching values into rows or columns instead of a single cell, which works great if you look up values across multiple Worksheets.

Thanks for being us today! Stay tuned and follow our latest function guides and tutorials!

Advanced XLOOKUP Formula Examples

Have you problems with the function?

Check our definitive guide on fixing your formula if it is not working.

Additional resources