There are several lookup functions in Excel to perform various lookup and searching operations easily and swiftly. Today we are going to show you how to use a lookup function called: the XLOOKUP function in Excel. For the session, we are using Excel 365.

## Introduction to Excel XLOOKUP Function

**Summary:**

Searches a range or an array for a match and returns the corresponding item from a second range or array.

**Syntax:**

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

**Arguments:**

Argument |
Required/Optional |
Explanation |
---|---|---|

lookup_value |
Required | It’s the specified value that needs to be searched for in the data table. |

lookup_array |
Required | The lookup_array is a range of cells or an array where the lookup_value will be searched for. |

return_array |
Required | It’s the second range of cells or an array from where the output data will be extracted. |

if_not_found |
Optional | If the lookup value is not found, a customized message can be inserted using this argument in text format. |

match_mode |
Optional | It defines if the function will look for an exact match based on specified criteria or a wildcard character match. |

search_mode |
Optional | It denotes the search order (in ascending or descending order, from last to first or first to last). |

**Return Parameter:**

**The XLOOKUP function** allows you to look for a value in a dataset and return the corresponding value in some other row/column.

**Versions:**

This function is available in Excel 2021, Excel 365, and Excel online. Despite the fact that Excel 2016 and Excel 2019 lack this function, you can still use workbooks that contain it in these versions. However, they must be originally created in any newer version.

## How to Use XLOOKUP Function in Excel: 11 Practical Examples

For example, we have a **movie **dataset of names with respective release **years **and **genres**. Using this dataset, we will show you different ways of using **the XLOOKUP function** in Excel.

### 1. Use the XLOOKUP Function for Exact Match

The basic task of **XLOOKUP **is to provide the **matched value **corresponding to the **lookup_value**. Here, we will perform a basic task. We will provide the movie name, and derive the genre using **the XLOOKUP function.**

To do that follow the steps given below.

**Steps:**

- Firstly, select
**Cell G5**and insert the following formula.

`=XLOOKUP(F5,B5:B9,D5:D9)`

- Then, press
**Enter**to get the**lookup value.**

**Cell F5**contains the

**lookup_value**, and cell ranges

**B5:B9**and

**D5:D9**are the

**lookup_array**and the

**return_array**respectively. However, we have not set any value for

**match_type**. As, by default, it checks

**Exact Match**.

### 2. Find an Approximate Match Using the Excel XLOOKUP Function

Another basic operation for **XLOOKUP **is to find an **approximate match**. Our example dataset has products with prices and discounts. Now, we will find the discount for the given price.

Go through the steps given below to do it on your own.

**Steps:**

- To start with, insert the following formula in
**Cell G5**.

`=XLOOKUP(F5,C5:C9,D5:D9,,-1)`

- After that, press
**Enter**to find the corresponding value of**Discount**.

**-1**as the

**match_type**field. Our inserted

**lookup_value**was

**$550**which was not on our list. Since we used

**-1**, this gave the

**nearest smaller value**. So we have found a discount of

**$500**.

- However, the
**approximate matches**can be done by another approach. - To do that, insert the following formula in
**Cell G5**.

`=XLOOKUP(F5,C5:C9,D5:D9,,1)`

- Then, press
**Enter**.

**1**as our

**match_type**field. That’s why this returned the

**nearest larger value**.

### 3. Return Multiple Values by Applying the XLOOKUP Function in Excel

We can also find the multiple values using **the XLOOKUP function**.

Now, we are going to use the previously introduced movie dataset (in addition we have added the Lead Actor column). Here we will find all the listed details for a given movie name.

**Steps:**

- Firstly, select
**Cell C12**and insert the following formula.

`=XLOOKUP(B12,B5:B9,C5:E9)`

- Next, press
**Enter**to get all the provided details of that**movie**.

**B12**as

**lookup_value**, and cell ranges

**B5:B9**and

**C5:E9**are the

**lookup_array**and the

**return_array**respectively.

**Read More:** How to Use XLOOKUP to Return Blank Instead of 0

### 4. Apply the Excel XLOOKUP Function for Multiple Criteria

The **XLOOKUP **function can be used for a multi-way (**multiple criteria**) lookup.

We have selected a dataset of the roll of honor of 5 European leagues. Now, we will set the name of the league and the status as criteria and look for the corresponding Team.

Here are the steps.

**Steps:**

- In the beginning, insert the following formula in
**Cell G7**.

`=XLOOKUP(G6,C4:D4,XLOOKUP(G5,B5:B9,C5:D9))`

- After that, press
**Enter**.

🔎** How Does the Formula Work?**

- In this example, we have used two
**XLOOKUP**. Firstly, within the**first XLOOKUP**, we checked the**status**of the**team**. - Then, we have set the
**second XLOOKUP**at the**return_array**field. - Here, within this second
**XLOOKUP**function, we have checked the**league**name and set the**team names**as the**return_array**. - Finally, we have found the
**name**of the**team**.

### 5. Execute Horizontal Lookup Using XLOOKUP Function

We can exercise **horizontal lookup **using **the XLOOKUP function**.

Here, we have a dataset of several players with their goals and assists. Now, we will find the goals and assists for a given player. The name of the player will be our lookup_value and stored in Cell C9.

Here are the steps.

**Steps:**

- To start with, insert the following formula in
**Cell C10**.

`=XLOOKUP(C9,C4:F4,C5:F6)`

- After that, press
**Enter**.

**XLOOKUP**function, we inserted

**Cell C9**as

**lookup_value**, cell ranges

**C4:F4**and

**C5:F6**as

**lookup_array**and

**return_array**respectively.

### 6. Use Nested XLOOKUP Formula in Excel

**XLOOKUP **can be used with other functions for nested lookup.

Let’s imagine, from the scorer dataset, we want to find the goal involvement (sum of goals and assists) for a given player.

Now, to calculate the **Goal Involvement **for a given **Player **can go through the following steps.

**Steps:**

- Firstly, select
**Cell C9**and insert the following formula.

`=SUM(XLOOKUP(C8,C4:F4,C5:F5),XLOOKUP(C8,C4:F4,C6:F6))`

- Then, press
**Enter**.

🔎** How Does the Formula Work?**

- Here, the two
**XLOOKUP**functions will provide**goals**and**assists**respectively for a**player**. - Then,
**the SUM function**will add them together and provide the result.

### 7. Solve #N/A Error Using XLOOKUP Function in Excel

Through **XLOOKUP **we can find the value we are aiming for.

From our movie dataset if we set a value that is within our table then we will find the value. However, if we set a movie name that is not on the table, then the #N/A error occurs.

Here, we have provided **Godzilla **as the **movie **name. And the error occurred.

To eradicate the error, follow the steps given below.

**Steps:**

- In the beginning, type the following formula in
**Cell G5**.

`=XLOOKUP(F5,B5:B9,D5:D9,"Not Found")`

- Then, press
**Enter**to execute the formula.

**“Not Found”**as the

**if_not_found**value. Since our provided

**movie**was

**not in**the

**dataset**, we have found the message

**“Not Found”**.

### 8. Utilize XLOOKUP with Different Functions for Complex Criteria

Now, we will show you how to utilize **the XLOOKUP function** along with other functions such as **LEFT**, **NOT**, and **MONTH** functions for complex criteria.

Suppose we have a dataset containing some bank Account No, their corresponding Country the amount of cash, and the Date of transaction of those accounts.

Now, you want to look for the data for the account that begins with “x” and Country is “Australia” and the month is not March.

To find out the data, follow the steps given below.

**Steps:**

- Firstly, select
**Cell B13**and insert the following formula.

`=XLOOKUP(1,(LEFT(B5:B10)="x")*(C5:C10="Australia")*NOT(MONTH(D5:D10)=3),B5:E10)`

- Then, press
**Enter**.

🔎** How Does the Formula Work?**

- Here, the
**LEFT**function will check if there is**“x”**on the**left**of the given cell range**B5:B10**. - Additionally, the
**MONTH**function will find out the**month**value of the cell range**D5:D10**. - Then, the
**NOT**function will check if the resultant of the MONTH is not equal to**3**. - Finally, using the results of these
**functions**, the**XLOOKUP**will return the**desired data****range**.

### 9. Apply Excel XLOOKUP Function to Find Partial Match Using Wildcard

You can use the wildcards to look for a value in the **XLOOKUP **formula. We usually use the **asterisk (*) **sign as **wildcards**.

Here, in the movie dataset, we will find the genre of a given movie with a wildcard.

**Steps:**

- In the beginning, insert the formula given below in
**Cell G5**.

`=XLOOKUP(F5,B5:B9,D5:D9,,2)`

- Finally, press
**Enter**.

**the XLOOKUP function**, we inserted

**2**as

**match_mode**for partial matches using asterisks.

### 10. Get the Last Occurrence Using XLOOKUP in Reverse Order

Now, suppose a value occurs several times in a dataset. However, you want to find out the corresponding value of the lookup value from its last occurrence. You can do this using **the XLOOKUP** **function** in reverse order.

Here, in our dataset Pants are sold 2 times. However, we want to find out the price of the pants in the last occurrence.

To do that, go through the steps given below.

**Steps:**

- Firstly, insert the following formula in
**Cell G5**.

`=XLOOKUP(F5, C5:C9, D5:D9, ,,-1)`

- Then, press
**Enter**. - Thus, you will find the
**Price**of the**pants**in the**last occurrence**.

### 11. Perform Left Lookup Applying Excel XLOOKUP Function

In the last example, we will show you how you can use **the XLOOKUP function** to **lookup **for a value from the **left**.

Suppose from the movie dataset, you want to find a movie name using Action as Genre.

Follow the steps given below to do that.

**Steps:**

- To start with, insert the formula given below in
**Cell G5**.

`=XLOOKUP(F5,D5:D9,B5:B9)`

- After that, press
**Enter**.

**Cell F5**is the

**look_up**value, cell range

**D5:D9**is the

**lookup_array,**and cell range

**B5:B9**is the

**return_array**.

**Practice Section**

In the article, you will find an Excel workbook like the image given below to practice on your own.

**Things to Remember**

- You can also insert or type the
**lookup_value**directly within**the XLOOKUP function**.

**Download Practice Workbook**

You are welcome to download the practice workbook from the link below.

## Conclusion

That’s all for today. We have tried showing you how you can use the XLOOKUP function in Excel. You can use the function to find the value from an array or reference. Hope you will find this helpful.

**<< Go Back to Excel Functions | Learn Excel**