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.
You are welcome to download the practice workbook from the link below.
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.
Searches a range or an array for a match and returns the corresponding item from a second range or array.
XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
|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
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.
We will perform a basic task here, provide the movie name, and derived the genre using the XLOOKUP function. Our formula will be
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.
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.
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.
We will find the discount for the given price. Our formula will be like the below one
We have set the values using Cell Reference. And at the match_type field inserted
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.
This will return the nearest larger value.
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).
Here we will find all the listed details for a given movie name. To do that our formula will be
We have set C4:E8 as the return_array. You can see almost the entire table (except the Movie column) in our return_array.
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.
We will set the criteria like the name of the league and the status.
To find the value considering our two criteria, we are going to use the below formula
We have used two XLOOKUP.
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.
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.
Here we have set the lookup_value, lookup_array, and return_array.
We have found the goals and assists by Ronaldo.
No worries Messi fans, we will search for him also. Haha!
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.
Our formula will be
Two XLOOKUP functions will provide goals and assists respectively for a player. Then the SUM function will add them together and provide the result.
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.
But if we set a movie name that is not on the table, then the #N/A error occurs.
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.
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”.
3. Quick Notes
- We can insert the lookup_value directly within the XLOOKUP.
We have set “Ronaldo” at the lookup_value. And we have found the result as expected.
- The wildcards are also can be used as search value. For wildcards, we usually use the asterisk (
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.
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.