How to Use XLOOKUP in Excel

This guide will discuss how to use XLOOKUP in Excel.

The rules for using the XLOOKUP in Excel are the following:

• The XLOOKUP function only has three required arguments to perform its search successfully. And there are three optional arguments to customize or specify the search the function performs. 
• Unlike the VLOOKUP function, the XLOOKUP function can search vertically and horizontally, from the first or last value. 
• Furthermore, the XLOOKUP function returns an #N/A error when it cannot find a match. 
• The XLOOKUP function will search for an exact match by default unless stated otherwise. And the XLOOKUP function will search starting from the first value in the data set by default. 

The XLOOKUP function will search a specific range or an array for a match and return the corresponding value from a second range or array. So it performs similarly to the VLOOKUP function. But, the XLOOKUP function makes up for the weak points and disadvantages of the other lookup functions. 

Let’s take a sample scenario. 

Suppose you are creating an employee directory. Then, you were asked to pull out specific employees from the directory based on their employee IDs. Rather than manually searching for it, you opted to use the XLOOKUP function to make the process easier and more efficient. 

Great! Before we learn more about how to use XLOOKUP in Excel, let’s first understand the parts of the XLOOKUP function.

The Anatomy of the XLOOKUP Function

The syntax or the way we write the XLOOKUP function is as follows:

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• XLOOKUP() is our XLOOKUP function. And this function searches for a match in a range or an array. Then, it will return the matched value. 
• lookup_value is a required argument. And it refers to the value or data we are searching for.
• lookup_array is also a required argument. So it refers to the range or array we will search in.
• return_array refers to the range or array to return. And this is a required argument. 
• if_not_found is an optional argument. And this will be returned if a match is not found. Additionally, this can be a number, boolean value, text string, or cell reference. 
• match_mode is also an optional argument. And this specifies how to match or search the lookup_value in the lookup_array.
• search_mode refers to the specific search mode to use. And this is an optional argument. Furthermore, the function will search from first to last by default. 

Awesome! Now let’s move on and discuss a real example of how to use XLOOKUP in Excel.

A Real Example of Using XLOOKUP in Excel

Let’s say we have an employee directory recently made. And we are tasked to search for some specific employee based on the given employee ID. Firstly, let’s see our initial data set:

Additionally, the XLOOKUP function has several key features that make it easier and better than the VLOOKUP function. Firstly, the XLOOKUP function is more dynamic and straightforward in searching for the data. Secondly, the XLOOKUP function searches for the exact match by default.

Thirdly, the XLOOKUP function supports situations where the value is not found. Next, the XLOOKUP function has several optional arguments to customize the search.

After learning the difference between the VLOOKUP function and the XLOOKUP function, let’s finally use the function. 

In this case, we are searching for specific employees based on their employee IDs. So we will utilize the XLOOKUP function to perform this task in Excel. 

Afterward, our data set would look something like this:

You can make your own copy of the spreadsheet above using the link attached below. 

Awesome! Now let’s learn the steps of how to use XLOOKUP in Excel.

How to Use XLOOKUP in Excel

In this section, we will learn the step-by-step process of how to use XLOOKUP in Exel. Each step will have a detailed explanation and pictures to guide you along the way. 

1. Firstly, we will select an empty cell. In this case, we will select cell E2. Then, this will serve as our search bar, where we will type the employee ID number.

2. Beside that cell, type in “=” the equal sign and XLOOKUP to start the function. Then, select the cell containing the value you are looking for. In this case, we will select cell E2.

Next, we need to determine our lookup_array. In this case, our employee directory is located at A2:A11. After, input the return array, which in this case would be B2:B11. So our entire formula would be “=XLOOKUP(E2,A2:A11,B2:B11)”. Lastly, press the Enter key to return the results.

3. And tada! We successfully found a match to the inputted employee ID.

4. We can also input the optional argument if we need them. For example, we can add a not_found argument in cases where the employee is not found in the directory. In this case, we will input the message “not found” if a match is unavailable.

So the entire formula would become “=XLOOKUP(E2,A2:A11,B2:B11,”not found”)”.

5. Furthermore, we can set a different match_mode argument instead of the default exact match searches. In this case, we will input “-1” for our formula to search for the exact match or the next value below it. So our entire formula becomes “==XLOOKUP(E2,A2:A11,B2:B11,”not found”,-1)”.

6. Another argument we can include is the search_mode. And this argument allows us to search for specific values that are necessarily not the first value found. For example, we will input the value “2” which will conduct the search in ascending order.

In this case, our entire formula becomes “==XLOOKUP(E2,A2:A11,B2:B11,”not found”,-1,2)”.

7. And tada! We have successfully used XLOOKUP in Excel.

And that’s pretty much it! We have discussed how to use XLOOKUP in Excel. Now that you have learned how to perform and use this function, you can apply and use it whenever you need to search for something within your data set.

Are you interested in learning more about what Excel can do? You can now use the XLOOKUP function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe