XLOOKUP function
XLOOKUP is an Excel function that helps us look up a value in a vertically or horizontally organised table. XLOOKUP supports many features such as exact matching, approximate matching, wildcard matching etc.
It is used to find the corresponding value in the table based on the given value.
Before learning XLOOKUP, let us first understand the syntax and parameters passed into the function.
Syntax of the XLOOKUP function
XLOOKUP(lookup_value,lookup_array,return_array,[not_found],[match_mode],[search_mode])
Arguments of the XLOOKUP function
There are six arguments in the XLOOKUP function. The first three arguments are compulsory, and the following three arguments are optional.
1. lookup_value the value you are looking for
2. lookup_array the array or range to search
3. return_array the array or range to return
4. if_not_found the value to return if not found
5. match_mode Used to specify the match type.
- 0 stands for Exact match
- -1 for Exact match, else return the next smaller item
- 1 for Exact match, else return the next greater item
- 2 for a wildcard match
- The default is 0, which stands for Exact match.
6. search_mode the mode of searching
- 1 represents search from first to last
- -1 represents search from last to first
- 2 performs a binary search in the data sorted in ascending order
- -2 performs a binary search in the data sorted in descending order
- The default is 1, which represents the search from first to last
Using XLOOKUP in Excel
The purpose is to look up the table vertically as well as horizontally.
Vertical LOOKUP
Consider the table given below of Hotel, Prices and Location.
We want to find the cost of Hotel Mariot.
We can find the cost by entering the following command.
=XLOOKUP(E3,A4:A10,B4:B10)
Here E3 stands for a cell having Mariot Hotel.
The lookup_array is from A4:A10, which represents the hotels in the sheet.
Finally, we want to return the cost. The return_array is thus from B4:B10, representing the sheet's costs.
Found the cost of the hotel
Now we could have found the cost using VLOOKUP too. So what is special in XLOOKUP is that the lookup_array can be horizontal as well as vertical.
Horizontal LOOKUP
Let us look at an example for the horizontal array.
Let us find the cost for Bombay City.
The syntax to find the cost for Bombay city would be
=XLOOKUP(A20,B15:E15,B16:E16)
Here A20 represents the cell for which we are finding the cost.
B15:E15 represents the cities array
B16:E16 represents the cost array.
The syntax of XLOOKUP for horizontal is exact the same as the vertical lookup
XLOOKUP for horizontal LOOKUP
Thus we saw that xlookup works for horizontal as well as vertical tables.
Last to First Search
Consider the below hotels and city. We want to retrieve the last searched value for Taj Hotel.
The syntax to search from last to first is given below
=XLOOKUP(E3,A4:A11,B4:B11,,,-1)
The ,, represents that the 4th and 5th arguments are not given. In that case, Excel automatically takes the default arguments.
The 6th argument is -1, which represents the last to the first match.
Retrieved the last cost for Taj Hotel
Wildcard Matching
XLOOKUP also supports wildcard matching, just like VLOOKUP and HLOOKUP functions.
We can search for wildcard matching by giving argument 2 in the XLOOKUP function.
Suppose we want to find the cost of the hotel starting with Lake.
By entering just the hotel, Lake would give an error. The reason is that it searches for Lake in the Hotel and it would not find a matching hotel.
To use wildcard matching, we must enter “Lake*” which stands for Lake, and then a group of characters.
Thus Lake* would match for Lake Palace, Lake P, LakePalace, etc.
The syntax is given below
=XLOOKUP(E3,A4:A10,B4:B10,,2)
The 4th argument is given empty by giving two “,” consecutively.
Wildcard matching
Fetching an Entire Record
XLOOKUP can be used to fetch the entire record.
Consider the following example.
Here we want to fetch the Cost and Location Both.
Both of them can be fetched by entering the following command.
=XLOOKUP(E3,A4:A10,B4:C10)
Command to fetch multiple records in XLOOKUP
Fetched multiple records
Advantages of XLOOKUP function
The benefits of the XLOOKUP function are:
- It works for both horizontal and vertical lookup_array.
- It can be used to fetch the entire record.
- It helps us to match wildcard matching, approximate matching, exact matching. It can also perform binary_search in ascending order as well as descending order.
- The return_array can be to the left or right of the lookup_array.
- XLOOKUP also helps us in the last to first search and vice versa.
FAQs
1. What is the XLOOKUP function?
XLOOKUP is an Excel function that helps us look up a value in vertically as well as horizontally organised tables. It also helps us for fetching approximate matches wildcard matching. It is fast and efficient and thus saves us the most time.
2. What are the advantages of using the XLOOKUP function?
The advantage of using the XLOOKUP function is that the change in the value of the table will automatically update the output of the XLOOKUP function.
3. What functions are similar to the XLOOKUP function?
The function HLOOKUP is used for lookup when a horizontal table is given. Another function, VLOOKUP, works for vertical tables.
4. What are the advantages of XLOOKUP over VLOOKUP and HLOOKUP?
The XLOOKUP function works for both vertical and horizontal tables. Moreover, the search_array may not be the first column in the table. Also, XLOOKUP can be used to fetch the entire record.
5. What does the XLOOKUP do when there are multiple matches?
The XLOOKUP function returns the value corresponding to the first matched result. However, to retrieve the last matched result, we can change the search_mode to -1.
Key Takeaways
We have extensively discussed the Microsoft XLOOKUP function.
We hope that this blog has helped you enhance your knowledge regarding the XLOOKUP function, and if you would like to learn more about the various useful functions of Excel, check out our articles:
Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc.
Enrol in our courses and refer to the mock test and problems available.
Take a look at the interview experiences and interview bundle for placement preparations.
Do upvote our blog to help other ninjas grow.
Happy Learning!!!
85+ registered 