Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
How to access XLOOKUP function?
3.
XLOOKUP function
3.1.
Syntax of the XLOOKUP function
3.2.
Arguments of the XLOOKUP function
4.
Using XLOOKUP in Excel
4.1.
Vertical LOOKUP
4.2.
Horizontal LOOKUP
4.3.
Last to First Search
4.4.
Wildcard Matching
4.5.
Fetching an Entire Record
4.6.
Advantages of XLOOKUP function
5.
FAQs
6.
Key Takeaways
Last Updated: Mar 27, 2024
Easy

XLOOKUP Function

Author HET FADIA
3 upvotes
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

In Excel, we need to find the lookup values corresponding to a value in a table. For example, we may need to find a person corresponding to his id. Finding thousands of values in a table consisting of thousands of values becomes tedious.

Microsoft Excel provides an XLOOKUP function by which we can do this task easily and efficiently. We just need to call the function, and we can easily fill in Excel the corresponding values.

To brush up on your knowledge,  you can read the articles HLOOKUP function and VLOOKUP function on Coding Ninjas Studio.

How to access XLOOKUP function?

XLOOKUP function is only available to the users of Office 365. Students and Educators have free access to Office 365.

If you do not have access to Office 365, you can get access to the XLOOKUP function by following the below steps:

1. Go to the following GitHub site and install the xlookup library ExcelDna.XFunctions64.xll.

2. Go to Files on the top left corner

3. Now go to options in the bottom left corner

 

4. Go to Add-Ins and Select Go

 

5. Click on Browse and Select the Excel File.

After doing this, we will be able to use the XLOOKUP function.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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!!!

Previous article
VLOOKUP Function
Next article
Count Function
Live masterclass