Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconData Sciencebreadcumb forward arrow iconWhat is XLOOKUP in Excel? How is it better than VLOOKUP?

What is XLOOKUP in Excel? How is it better than VLOOKUP?

Last updated:
21st Aug, 2022
Views
Read Time
7 Mins
share image icon
In this article
Chevron in toc
View All
What is XLOOKUP in Excel? How is it better than VLOOKUP?

The Excel lookup function family is a huge one. XLOOKUP in Excel is the newest member of this family. Some of the other prominent members of this family include HLOOKUP, VLOOKUP, LOOKUP, INDEX + MATCH, etc. If you are using Excel 2021 or Excel 365, you can use XLOOKUP instead of VLOOKUP. Excel experts opine that the XLOOKUP formula is convenient and easy to use. 

If you want to have an idea of XLOOKUP, you can think of this function as VLOOKUP 2.0. You can also check various XLOOKUP examples to understand this function better. 

Here we will shed light on XLOOKUP in Excel and the XLOOKUP formula

Learn online data science courses from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

What is XLOOKUP in Excel?

XLOOKUP in Excel is a powerful search tool for finding particular values from a cell range. It pulls matching data (it could be the closest available match or exact matches) from an Excel data set. There may be more than one matching result. In such a situation, the last or the first result in the set is returned. However, this will also depend on the arguments that you use. 

The XLOOKUP formula works as a custom search tool, which is flexible to different kinds of data demands in custom-built Excel spreadsheets. This is a new function available for users of Microsoft 365. 

Explore our Popular Data Science Courses

When you use XLOOKUP formula, you just have to provide three basic parameters, and MS Excel can perform the other three functions:

  • The value which you are looking for
  • The list where you will find this value
  • The list from which you want the result
  • [optional] value if it is not found

An XLOOKUP example for easy understanding:

=XLOOKUP (“Alice”, Marketing[Marketing Person], Marketing[Net Marketing])

Returns [Net Marketing] for Alice if the name is there in the [Marketing Person’s] column. 

When you compare VLOOKUP, you need not specify column_number or true/false for performing the search. 

This means that XLOOKUP can search anywhere in the datasheet to find the result and not just on the left. You don’t have to opt for complex VLOOKUP formulas or complicated INDEX+MATCH formulas. 

Syntax of XLOOKUP formula and function in MS Excel

The syntax of XLOOKUP formula and function in MS Excel is as follows: 

XLOOKUP (value, lookup_array, return_array, [if not found], [match_mode], [search_mode])

Parameters or Arguments

value – This is the value that you have to search for in the lookup_array

lookup_array – This is the range of cells or array to search for value

return_array – The range of cells or arrays from which a corresponding value will be returned based on the position of value in lookup_array

if_not_found – Optional. The value to return if no match is found. When this parameter is omitted, the function will provide a #N/A error (same as HLOOKUP and VLOOKUP functions).

match_mode – This is optional. This is the type of match to perform. It can be of these values:

match_modeExplanation
0Exact match
-1If no exact match is found, it returns next smaller item
1If no exact match is found, it returns next larger item
2Wildcard match by using special characters like *, ?, ~

search_mode – This is optional. This is the kind of search that you have to perform. It can be one of the following values:

search_modeExplanation
1Search begins at the first item in the lookup_array (default)
-1Reverse search in which the search begins at the last item in the lookup_array
2Binary search where items in lookup_array must be arranged in ascending order
-2Binary search where items in lookup_array must be arranged in descending order

Step by step guide of using XLOOKUP formula in MS Excel

Follow the following steps if you want to create a formula using XLOOKUP function:

Step 1 – Selecting an empty cell

When you want to insert the XLOOKUP function in a new formula, first open an Excel sheet, containing your data set and select an empty cell. Alternatively, you have to open a new spreadsheet. 

Once you select the cell, you have to press the formula bar on the ribbon bar so that you can start writing and editing as required. When you see that the cursor blinks on the ribbon bar, it means the cell is active, and you can start editing. Now you can insert a new XLOOKUP formula

Step 2 – Determining the search (lookup) criteria

You must follow a specific structure and syntax when using a new formula using XLOOKUP function. You have to begin by typing the formula. Start typing =XLOOKUP( in the ribbon bar. Now insert your lookup criteria. This is the information you are looking for. 

XLOOKUP supports texts and number strings. But most users specify a cell reference. So, you can change the search criteria per your preference without changing the XLOOKUP formula directly. By default, XLOOKUP will try to find the exact match. 

Step 3 – Determining the data set using lookup_array and return_array

After inserting the lookup value in the XLOOKUP formula, you must identify your lookup_array and return_array cell ranges. The lookup_array is the column or row which might contain your search query or the nearest approximate result. The return_array is the column or row where you will see your return result. 

At this stage, you can close your XLOOKUP formula with a closing parenthesis if you don’t want to add a custom error message, change the search value order, or use a different search type.  

Step 4 (optional) – Adding custom error message using not_found

If you intend to find an exact match for your lookup value, but it is not there in the lookup_array range, Excel will show an #NA error response. To avoid this problem, you must insert an optional not_found argument in the XLOOKUP formula. The not_found argument can be a text string within quotes (“not found”), a numerical value, a cell reference, or a boolean value (true or false). 

Step 5 (optional) – Adding approximate, exact, or Wildcard Search Matches using match_mode

XLOOKUP in Excel is designed for searching exact matches by default. But this can be changed by adding an optional argument match_mode to the XLOOKUP formula. As a wildcard match, this is the closest and first approximation. If you want to change the search order, you have to add a search_mode argument in the next step. 

Step 6 (optional) – Determining the value return order by using search_mode

In an XLOOKUP formula, an optional search_mode argument is the final supported argument. This changes the whole order in which XLOOKUP finds a matching result. By default, Excel will try and match the first possible value. 

Reasons why XLOOKUP in Excel is better than VLOOKUP

There are many reasons why XLOOKUP formula is better than VLOOKUP. Here are a few:

1. XLOOKUP tries to find exact match by default

In VLOOKUP, you have to mention FALSE as the last parameter for obtaining the correct result. However, in XLOOKUP, you can find the exact match by default. Use the match mode parameter for changing the lookup behavior. 

2. XLOOKUP makes the formula dynamic 

In MS Excel, XLOOKUP formula is dynamic and straightforward. Most importantly, the formula is less error-prone. You just have to write =XLOOKUP( and you will get the result. If the value is not found, you will get a #N/A error. 

3. There are optional parameters in XLOOKUP in Excel

The XLOOKUP formula offers optional parameters to search for special situations. You can search from top to bottom or perform wild card searches. The options of searching sorted lists are faster. 

4. XLOOKUP is easy to type

You have to type =XL and the function initiates. This is way easier than operating other functions and formulas. 

5. XLOOKUP in Excel returns reference as output

XLOOKUP formula returns reference as output and not the value. For normal users, this might not be something significant, but for pro-Excel users, it is a formula that returns refs. This means, XLOOKUP can be combined with other formulas in many ways. 

Read our popular Data Science Articles

Conclusion

Lookup functions are among the best options when you are looking to search data through complicated Excel spreadsheets. XLOOKUP in Excel is among the popular lookup functions. For data analysts, the XLOOKUP formula is an excellent choice since it allows them to manipulate the data for delivering the desired results. 

Join a class with upGrad’s Certificate Program in Data Analytics 

Making a career in data analytics is a lucrative opportunity in the current industry. Data analysts work with huge volumes of data for extracting crucial information. To hone your skills in data analytics, you must join upGrad’s Data Analytics Certificate Program. Powered by Fullstack Academy, you will get a Caltech data analytics certificate upon completing the course. You will be able to learn data visualization skills, Python, and SQL through the course, which will pave your way for making a great data analyst. 

Profile

Rohit Sharma

Blog Author
Rohit Sharma is the Program Director for the UpGrad-IIIT Bangalore, PG Diploma Data Analytics Program.

Frequently Asked Questions (FAQs)

1In which Excel version will you get XLOOKUP?

XLOOKUP function is only available in Excel for Office 365.

2Is the XLOOKUP formula better than HLOOKUP or VLOOKUP?

It is interesting to note that the XLOOKUP formula replaces both HLOOKUP and VLOOKUP function and formula. The approximate matches in XLOOKUP are smarter and more appropriate. XLOOKUP is able to search data in all directions.

Explore Free Courses

Suggested Blogs

6 Phases of Data Analytics Lifecycle Every Data Analyst Should Know About
77926
What is a Data Analytics Lifecycle? Data is crucial in today’s digital world. As it gets created, consumed, tested, processed, and reused, data goes
Read More

by Rohit Sharma

19 Feb 2024

Sorting in Data Structure: Categories & Types [With Examples]
135231
The arrangement of data in a preferred order is called sorting in the data structure. By sorting data, it is easier to search through it quickly and e
Read More

by Rohit Sharma

19 Feb 2024

Data Science Vs Data Analytics: Difference Between Data Science and Data Analytics
66714
Summary: In this article, you will learn, Difference between Data Science and Data Analytics Job roles Skills Career perspectives Which one is right
Read More

by Rohit Sharma

19 Feb 2024

13 Exciting Python Projects on Github You Should Try Today [2023]
44206
Python is one of the top choices in programming languages among professionals worldwide. Its straightforward syntax allows software developers and dat
Read More

by Hemant

19 Feb 2024

Top 15 Python AI & Machine Learning Open Source Projects
35813
Machine learning and artificial intelligence are some of the most advanced topics to learn. So you must employ the best learning methods to make sure
Read More

by Pavan Vadapalli

19 Feb 2024

Top 21 Python Developer Skills You Must Need To Become a Successful Python Developer
77978
Its intuitive syntax, extensive libraries and versatile integration capabilities have fueled incredible growth across web development and scientific c
Read More

by Rohit Sharma

19 Feb 2024

Most Frequently Asked NumPy Interview Questions and Answers [For Freshers]
28446
If you are looking to have a glorious career in the technological sphere, you already know that a qualification in NumPy is one of the most sought-aft
Read More

by Rohit Sharma

19 Feb 2024

Top 30 Python Pattern Programs You Must Know About
32744
Summary Pattern in Python or “Python patterns” is an essential part of Python programming, especially when you are just starting out with using algor
Read More

by Rohit Sharma

19 Feb 2024

Top 12 Fascinating Python Applications in Real-World [2024]
154355
It is a well-established fact that Python is one of the most popular programming languages in both the coding and Data Science communities. But have y
Read More

by Rohit Sharma

19 Feb 2024

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon