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:

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

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.