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: **XLOOKUP **function in Excel. For the session, we are using **Excel 365**.

**Table of Contents**hide

## Download Practice Workbook

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

## 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.

## 11 Practical Examples of Using XLOOKUP Function in Excel

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

### 1. Use 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 Approximate Match Using 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 Applying 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.

### 4. Apply 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 **lookup **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 have 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 **goals involvement **(**sum** of **goals **and **assists**) for a given player.

Now, to calculate the **Goal Involvement **for a given **Player **can be found going 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 **and 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 lookup 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**.

**XLOOKUP**function, we inserted

**2**as

**match_mode**for

**partial matches**using

**asterisks**.

### 10. Get 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**.

**-1**as

**search_mode**to use the function in

**reverse order**.

### 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.

**Similar Readings**

**How to Find Duplicate Values in Excel using VLOOKUP****Using Excel to Lookup Partial Text Match [2 Easy Ways]****VLOOKUP and HLOOKUP combined Excel formula (with example)****How to Use Excel HYPERLINK Function (8 Examples)**

## Things to Remember

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

## 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.