### Table of contents

- What is Excel XLOOKUP Function?
- XLOOKUP Function Arguments
- XLOOKUP Formulas With Mandatory Arguments
- Use XLOOKUP to Overcome Limitations of VLOOKUP
- How to Use XLOOKUP vs. HLOOKUP
- How to Catch Errors With XLOOKUP
- XLOOKUP Array Formula and Horizontal Spill
- XLOOKUP Array Formula and Vertical Spill
- Nested XLOOKUP Functions
- How to Use XLOOKUP With Approximate Match Mode
- How to use XLOOKUP Wildcard Character Match Mode

## 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 l**ookup 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?