How to Use XLOOKUP Function in Excel (11 Practical Examples)

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.


Download Practice Workbook

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


Introduction to Excel XLOOKUP Function

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

  • 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).

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.

Examples of Using 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.

Use XLOOKUP Function for Exact Match in Excel

Here, we have used Cell Reference in our formula. 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.

Find Approximate Match Using Excel XLOOKUP Function

In this case, we inserted -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.

Here, we inserted 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)

Return Multiple Values Applying XLOOKUP Function in Excel

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

Excel XLOOKUP Function

In the formula, we inserted Cell 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.

Apply Excel XLOOKUP Function for Multiple Criteria

🔎 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)

Execute Horizontal Lookup Using XLOOKUP Function

  • After that, press Enter.

In the 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.

Use Nested XLOOKUP Formula in Excel

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

Solve #N/A Error Using XLOOKUP Function in Excel

In the formula, we have set “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)

Utilize XLOOKUP with Different Functions for Complex Criteria in Excel

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

Apply Excel XLOOKUP Function to Find Partial Match Using Wildcard

In the 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.

Get Last Occurrence Using XLOOKUP in Reverse Order in Excel

Here, we put -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.

Perform Left Lookup Applying Excel XLOOKUP Function

In the formula, 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.

Practice Section


Similar Readings


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.


Related Articles

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo