This guide will discuss **how to use XLOOKUP in Excel**.

##### Table of Contents

The rules for using the `XLOOKUP`

in Excel are the following:

- The
`XLOOKUP`

function only has three required arguments to perform its search successfully. And there are three optional arguments to customize or specify the search the function performs. - Unlike the
`VLOOKUP`

function, the`XLOOKUP`

function can search vertically and horizontally, from the first or last value. - Furthermore, the
`XLOOKUP`

function returns an #N/A error when it cannot find a match. - The
`XLOOKUP`

function will search for an exact match by default unless stated otherwise. And the`XLOOKUP`

function will search starting from the first value in the data set by default.

The `XLOOKUP`

function will search a specific range or an array for a match and return the corresponding value from a second range or array. So it performs similarly to the `VLOOKUP`

function. But, the `XLOOKUP`

function makes up for the weak points and disadvantages of the other lookup functions.

Let’s take a sample scenario.

Suppose you are creating an employee directory. Then, you were asked to pull out specific employees from the directory based on their employee IDs. Rather than manually searching for it, you opted to use the `XLOOKUP`

function to make the process easier and more efficient.

Great! Before we learn more about how to use `XLOOKUP`

in Excel, let’s first understand the parts of the `XLOOKUP`

function.

**The Anatomy of the XLOOKUP Function**

The syntax or the way we write the `XLOOKUP`

function is as follows:

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

Let’s take apart this formula and understand what each term means:

**=**the equal sign is how we activate any function in Excel.**XLOOKUP()**is our`XLOOKUP`

function. And this function searches for a match in a range or an array. Then, it will return the matched value.**lookup_value**is a required argument. And it refers to the value or data we are searching for.**lookup_array**is also a required argument. So it refers to the range or array we will search in.**return_array**refers to the range or array to return. And this is a required argument.**if_not_found**is an optional argument. And this will be returned if a match is not found. Additionally, this can be a number, boolean value, text string, or cell reference.**match_mod**e is also an optional argument. And this specifies how to match or search the lookup_value in the lookup_array.**search_mode**refers to the specific search mode to use. And this is an optional argument. Furthermore, the function will search from first to last by default.

Awesome! Now let’s move on and discuss a real example of how to use `XLOOKUP`

in Excel.

**A Real Example of Using XLOOKUP in Excel**

Let’s say we have an employee directory recently made. And we are tasked to search for some specific employee based on the given employee ID. Firstly, let’s see our initial data set:

Additionally, the `XLOOKUP`

function has several key features that make it easier and better than the `VLOOKUP`

function. Firstly, the `XLOOKUP`

function is more dynamic and straightforward in searching for the data. Secondly, the `XLOOKUP`

function searches for the exact match by default.

Thirdly, the `XLOOKUP`

function supports situations where the value is not found. Next, the `XLOOKUP`

function has several optional arguments to customize the search.

After learning the difference between the `VLOOKUP`

function and the `XLOOKUP`

function, let’s finally use the function.

In this case, we are searching for specific employees based on their employee IDs. So we will utilize the `XLOOKUP`

function to perform this task in Excel.

Afterward, our data set would look something like this:

You can make your own copy of the spreadsheet above using the link attached below.

Awesome! Now let’s learn the steps of how to use `XLOOKUP`

in Excel.

**How to Use XLOOKUP in Excel**

In this section, we will learn the step-by-step process of how to use `XLOOKUP`

in Exel. Each step will have a detailed explanation and pictures to guide you along the way.

1. Firstly, we will select an empty cell. In this case, we will select cell **E2**. Then, this will serve as our search bar, where we will type the employee ID number.

2. Beside that cell, type in “**=**” the equal sign and `XLOOKUP`

to start the function. Then, select the cell containing the value you are looking for. In this case, we will select cell **E2**.

Next, we need to determine our lookup_array. In this case, our employee directory is located at** A2:A11**. After, input the return array, which in this case would be **B2:B11**. So our entire formula would be “**=XLOOKUP(E2,A2:A11,B2:B11)**”. Lastly, press the **Enter **key to return the results.

3. And tada! We successfully found a match to the inputted employee ID.

4. We can also input the optional argument if we need them. For example, we can add a not_found argument in cases where the employee is not found in the directory. In this case, we will input the message “**not found**” if a match is unavailable.

So the entire formula would become “**=XLOOKUP(E2,A2:A11,B2:B11,”not found”)**”.

5. Furthermore, we can set a different match_mode argument instead of the default exact match searches. In this case, we will input “**-1**” for our formula to search for the exact match or the next value below it. So our entire formula becomes “=**=XLOOKUP(E2,A2:A11,B2:B11,”not found”,-1)**”.

6. Another argument we can include is the search_mode. And this argument allows us to search for specific values that are necessarily not the first value found. For example, we will input the value “**2**” which will conduct the search in ascending order.

In this case, our entire formula becomes “=**=XLOOKUP(E2,A2:A11,B2:B11,”not found”,-1,2)**”.

7. And tada! We have successfully used `XLOOKUP`

in Excel.

And that’s pretty much it! We have discussed how to use `XLOOKUP`

in Excel. Now that you have learned how to perform and use this function, you can apply and use it whenever you need to search for something within your data set.

Are you interested in learning more about what Excel can do? You can now use the `XLOOKUP`

function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.