How to use XLOOKUP function in Excel (7 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. For the session, we are using Excel 365.

XLOOKUP overview - Excel XLOOKUP Function

Practice Workbook

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

XLOOKUP Function

1. Basics of XLOOKUP

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

Syntax - 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 Description
lookup_value Required The value to search for
lookup_array Required The array or range to search
return_array Required The array or range to return
if_not_found Optional Where a match is not found, return supplied value
match_mode Optional Specify the match type

0 – Exact match

-1 – Exact or next smaller item.

1 – Exact or next larger item.

2 – A wildcard match

search_mode Optional Specify the search mode to use

1 – Perform a search starting at the first item. This is the default.

-1 – Perform a reverse search starting at the last item.

2 – Binary search values sorted in ascending order

-2 – Binary search values sorted in descending order

Versions

Workable in Excel 365

2. Use of XLOOKUP

I. Basic Fetch (Exact Match)

The basic task of XLOOKUP is to provide the matched value corresponding to the lookup_value. 

For example purpose, we have brought a movie dataset of names with respective releasing years and genres.

Basic exact match - Excel XLOOKUP Function

We will perform a basic task here, provide the movie name, and derived the genre using the XLOOKUP function. Our formula will be

=XLOOKUP(F4,B4:B8,D4:D8)

We have used Cell Reference in our formula. F4 contains the lookup_value, B4:B8 and D4:D8 are the lookup_array and the return_array respectively.

We have not set any value for match_type. By default, it checks Exact Match.

Exact match - Excel XLOOKUP Function

We have provided The Dark Knight as the lookup_value at the F4 cell.

Through the formula, we have looked for this value within the Movie column (B4:B8) and then set it to return value from the Genre column (D4:D8).

Our formula gave the genre for the provided movie.

Let’s change the lookup_value. 

Exact match 2 - Excel XLOOKUP Function

II. Basic Fetch (Approximate Match)

Another basic operation for XLOOKUP is to find an approximate match.

Our example dataset has products with prices and discounts.

Approximate match - Excel XLOOKUP Function

We will find the discount for the given price. Our formula will be like the below one

=XLOOKUP(F4,C4:C8,D4:D8,,-1)

We have set the values using Cell Reference. And at the match_type field inserted -1.

Approximate match -1 - Excel XLOOKUP Function

Our inserted lookup_value 550 which was not in our list. Since we used -1, this gave the nearest smaller value. So we have found a discount of 500.

The approximate matches can be done by another approach. We can use 1 at our match_type field.

=XLOOKUP(F4,C4:C8,D4:D8,,1)

This will return the nearest larger value.

Approximate match 1 - Excel XLOOKUP Function

Here we have found the discount value for 700.

III. Multiple Value Fetch

We can find the multiple values using XLOOKUP.

We are going to use the previously introduced movie dataset (in addition we have added the Lead Actor column).

Multiple value fetch - Excel XLOOKUP Function

Here we will find all the listed details for a given movie name. To do that our formula will be

=XLOOKUP(G4,B4:B8,C4:E8) 

We have set C4:E8 as the return_array. You can see almost the entire table (except the Movie column) in our return_array.

Multiple value fetch - Excel XLOOKUP Function

Our formula has returned multiple column values.

IV. Multi-Way Lookup

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.

multi-way fetch - data - Excel XLOOKUP Function

We will set the criteria like the name of the league and the status.

multi-way fetch - criteria - Excel XLOOKUP Function

To find the value considering our two criteria, we are going to use the below formula

=XLOOKUP(G5,C3:D3,XLOOKUP(G4,B4:B8,C4:D8))

We have used two XLOOKUP. 

multi-way fetch - result - Excel XLOOKUP Function

Within the first XLOOKUP, we have checked the status of the team. Then we have set the second XLOOKUP at the return_array field.

Within this second XLOOKUP function, we have checked the league name and set the team names as the return_array.  We have found the name of the team.

V. Horizontal Lookup

We can exercise horizontal lookup using the XLOOKUP function.

Here we have a dataset of several players with their goals and assists.

horizontal lookup - data - Excel XLOOKUP Function

We will find the goals and assists for a given player. The name of the player will be our lookup_value and stored in the C8 cell.

=XLOOKUP(C8,C3:G3,C4:G5) 

Here we have set the lookup_value, lookup_array, and return_array.

horizontal lookup - result - Excel XLOOKUP Function

We have found the goals and assists by Ronaldo.

No worries Messi fans, we will search for him also. Haha!

horizontal lookup - result 2 - Excel XLOOKUP Function

VI. Nested Use of XLOOKUP

XLOOKUP can be used with other functions as 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.

nested formula use - data - Excel XLOOKUP Function

Our formula will be

=SUM(XLOOKUP(C8,C3:G3,C4:G4),XLOOKUP(C8,C3:G3,C5:G5)) 

Two XLOOKUP functions will provide goals and assists respectively for a player. Then the SUM function will add them together and provide the result.

nested formula use - SUM - Excel XLOOKUP Function

Here for Messi, we have found his goals involvement.

VII. If No Value is Found

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.

Found - not found - Excel XLOOKUP Function

But if we set a movie name that is not on the table, then the #N/A error occurs.

N/A error - Excel XLOOKUP Function

We have provided GodZilla as the movie name. And the error occurred.

To eradicate the error we need to set value at the if_not_found field.

=XLOOKUP(F4,B4:B8,D4:D8,"Not Found") 

We have set “Not Found” as the if_not_found value.

Not Found message-Excel XLOOKUP Function

Since our provided movie was not in the dataset, we have found the message “Not Found”.

3. Quick Notes

  1. We can insert the lookup_value directly within the XLOOKUP.  

Direct input - Excel XLOOKUP Function

We have set “Ronaldo” at the lookup_value. And we have found the result as expected.  

Direct input result - Excel XLOOKUP Function

  1. The wildcards are also can be used as search value. For wildcards, we usually use the asterisk (*) sign.

Wildcards - Excel XLOOKUP Function

For wildcards to match, we need to use 2 at the match_type field. Our lookup_value is “*Sal*”, which means any value having “Sal” within the string. In our dataset, only Mo Salah has “Sal” in the string.

Wildcards result- Excel XLOOKUP Function

Conclusion

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

Feel free to comment if anything seems difficult to understand. Let us know any of your XLOOKUP function-related scenarios where you have stuck, we are ready to help.

shakil

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