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.
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.
- 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.
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.
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.
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.
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.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
