What is Excel XLOOKUP Function?

In this tutorial, you'll learn about the incredibly flexible and powerful XLOOKUP Excel function. To get started with XLOOKUP, watch the video tutorial first…

If you've been using Excel for any period, you are probably familiar with VLOOKUP. If that is true, you are in for a real intellectual treat right now. Because there is a new kid on the block, a function called XLOOKUP.

And if you are serious about developing your skills, I urge you to learn how to do XLOOKUP. In Excel, the more functions you are comfortable with, the more enjoyable your work gets. And the more productive you become.

Besides, I believe XLOOKUP is an amazing alternative to VLOOKUP. Not to mention that it can make a massive difference in your daily workflow because it is much more flexible and robust.

In fact, it is like several functions in one…

Let's say, if you took VLOOKUP, HLOOKUP, IFERROR, INDEX, and MATCH and mixed them together. Plus, if you added new almost magical capabilities. What would you get? The answer is the more powerful XLOOKUP function. 

So, if you are interested, let's get started with…

XLOOKUP Function Arguments

Some people call them attributes… there are six arguments, three mandatory and three optional.:

• lookup_value is what you want to look up,
• lookup_array is the range where you want to look it up, 
• return_array is the range where the result comes from,
• [if_not_found] is the result if XLOOKUP returns an error. It is like a built-in IFERROR function.
• [match_mode] you can also call it the match type. By default, XLOOKUP returns the exact match. 
• [search_mode] let's you specify the direction of the search. 

The picture below makes them easier to understand.

The next example shows you how to build…

XLOOKUP Formulas With Mandatory Arguments

Notice that the formula in cell B2 uses the required attributes only. Without them, the formula could not return a result.  

The next diagram will help you understand what the formula does and how it calculates… or returns its result.

So, this is almost like VLOOKUP. We look up a specific value in the left column and return a value from a column on the right in the same row. But XLOOKUP is more flexible than that. We can also lookup on the right and return from the left. Check out the next example. It shows you how to…

Use XLOOKUP to Overcome Limitations of VLOOKUP

With VLOOKUP function you would have to restructure data, so the lookup column is on the left. XLOOKUP does not have that limitation. Look at the image below.

The formula in cell E2 looks up the Zip code from cell D2. It searches the lookup array in column E and gets its result from the return array in column D.

And the results are on the next diagram.

As you can see, XLOOKUP lets you overcome limitations of VLOOKUP and work from left to right or from right to left. But it gets even better. The next use case teaches you…

How to Use XLOOKUP vs. HLOOKUP

Look at the formulas on the next image, in cells B2 and E2…

The E2 formula will look for the name Andrew in row 7 (the top row of the data set) and return the corresponding State from row 9. It works almost like an HLOOKUP formula.

The formula in B2 looks up the ID in the bottom row 11 and returns the City from row 8 above. You couldn't have done it with HLOOKUP. 

Check out the results are on the next image:

So, you can use XLOOKUP instead of VLOOKUP or HLOOKUP while overcoming the limitations of both legacy functions. With a vertical lookup, XLOOKUP works either left to right or right to left. With a horizontal lookup you can use it top down, or bottom up. 

But this is just the beginning. Let me show you…

How to Catch Errors With XLOOKUP 

That's where you can use the optional [if_not_found] argument. 

First, let's see what happens if XLOOKUP can not find the lookup value. Look at the picture below.

It's the notorious #N/A error. It is very confusing to a less experienced Excel user. Now, let's add the [if_not_found] argument… 

The “Unknown ID” is what we want to see instead of the #N/A if the formula cannot find the lookup value. This is how it displays the result if it is an error. This argument is like a built-in IFERROR function right inside the XLOOKUP.

But this is still only the beginning. XLOOKUP can return an entire list instead of a single value. Let's look at an example of such a formula.

XLOOKUP Array Formula and Horizontal Spill

An Excel formulas can return a single value or as a list of values. XLOOKUP function can do both things. Look at the image below.

This example shows how to make XLOOKUP formulas return arrays as the results. To pull it off, you need to specify a two-dimensional range for the return array argument. Just like the range B7:E11 above. This formula will return the entire row for the lookup value. Like this…

Notice that the formula lives in a single cell (B4), but the results are “spilled” into the adjacent cells C4:E4. Those cells need to be empty for the results to spill. 

XLOOKUP Array Formula and Vertical Spill

It also works if you want to do a horizontal lookup. Here's an example:

Look at the image below for the results of the formula in cell G5:

As you can see, the formula's lookup array is two-dimensional and the result that it returns is a list of values. I suppose you can think of several use cases when displaying a list like this is useful. 

When the result is a single value and not an array, the term is scalar value. 

Nested XLOOKUP Functions

You can use XLOOKUP's ability to return a list for more interesting and elegant solutions for calculating scalar results. For example, it allows you to perform a two-dimensional lookup. Let's see how you can do that… 

Notice that XLOOKUP has another XLOOKUP inside it. It's a lookup inside lookup. You can call it nested XLOOKUP formula because we use one XLOOKUP to get the return_array argument and then we feed the found dynamic return array to the other XLOOKUP. It's an impressive way to perform a lookup. 

How to Use XLOOKUP With Approximate Match Mode

You probably know that VLOOKUP has an optional argument range_lookup. If you leave it empty, VLOOKUP returns a what's called an approximate match.  

XLOOKUP returns the exact match by default. Which means, if it can not find the lookup value in the lookup range, it returns an error.  I have already shown you how to handle the errors with the if_not_found optional argument. 

But you can make XLOOKUP return an approximate match too.  Use the optional match_mode argument to set it up. In fact, it is more flexible than VLOOKUP or HLOOKUP on this front as well.

Let's take a look…

The formula in cell C2 does not specify the match_mode. As a result, we get the exact-match mode. Which means, if the exact value is missing, we get the error or the if_not_found argument.

You can use the following match modes to specify how you want to match the lookup values in the lookup array:

0  Exact match

-1 Exact match or next smaller item

1  Exact match or next larger item

2  Wildcard character match

Let's look at an example of the -1 or the exact-match-or-next-smaller-item match mode.

What happens here is when the lookup value is missing in the lookup array, we get the previous, smaller value. You can also call it the less-than match. This is just like the approximate match in VLOOKUP.

Now, let's see how the match mode 1, the exact match or next larger-item works.

Here, when the formula cannot find the exact match, it returns the next, larger value. We can call it the greater-than match. Notice, if there is no “next larger item,” the XLOOKUP formula returns an error or the if_not_found argument.

How to use XLOOKUP Wildcard Character Match Mode

What's the deal with the match mode 2? How is it useful? Let me show you. 

Here we use the ampersand, concatenation operator to add asterisks, wildcard characters, on the left and on the right of the lookup value. This allows the formula to perform a more “relaxed” lookup and search for a text string inside the values in the lookup array. The wildcard character match mode argument makes it possible.

And here's what the results look like:

It's an interesting option, isn't it?