Top 11 Examples of Using XLOOKUP in Excel – The Ultimate Guide

Excel is a strong tool that can assist you in many ways with data organization and analysis. The XLOOKUP function in Excel is among its most crucial features. You can use this flexible tool to look up a specific value in a group of cells and have the corresponding value from another column returned.

In Excel 2019 and later versions, the XLOOKUP function was added as a new feature. Due to its adaptability and simplicity of use, XLOOKUP in Excel has quickly become one of the most used functions for data analysis and manipulation.

 

SYNTAX

To use the XLOOKUP function, you need to know the syntax and the parameters involved. The syntax of the XLOOKUP in Excel is:

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

1. Lookup_value: The value you want to search for.
2. Lookup_array: The range of cells to search in.
3. Return_array: The range of cells to return the result from.
4. [if_not_found]: Value to return if no match is found. If left blank, no matches will return #N/A
5. [match_mode]: The type of match to perform (exact or approximate). This is an optional parameter, and the default value is 0 (exact match).
   1. 0 – Exact match. If none is found, return #N/A.
   2. -1 – Exact match or return the next smaller item.
   3. 1 – Exact match or return the next larger item.
   4. 2 – A wildcard match : *, ?, and ~ have special meaning.
6. [search_mode]: The direction of the search (left to right or right to left). This is an optional parameter, and the default value is 1 (left to right).
   1. 1 – Perform a search starting at the first item.
   2. -1 – Perform a reverse search starting at the last item.
   3. 2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
   4. -2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

 

Advantages of the XLOOKUP in Excel

1. Efficiency: When working with large datasets, the XLOOKUP in Excel outperforms the VLOOKUP and HLOOKUP functions in terms of speed and efficiency.
2. Flexibility: The XLOOKUP function has a special feature that was not present in earlier versions of Excel: it can conduct exact match and approximate match searches as well as right-to-left searches.
3. Accuracy: Even when working with complicated datasets and formulas, the XLOOKUP in Excel produces accurate results.
4. Simplicity: XLOOKUP in Excel is simple to use and comprehend, even for users with limited prior knowledge.

 

Now that we have covered the syntax and advantages of XLOOKUP, let us move on to the Top 11 examples of XLOOKUP in Excel –

Click here to watch the tutorial on how to use XLOOKUP in Excel and download the workbook to follow along –

 

Example 1 – Simple and exact match

In this example, we want to do a simple and exact match to get the Date of Joining for the Employee ID mentioned in cell G6 using XLOOKUP in Excel.

Follow the steps below to

STEP 1: Enter XLOOKUP in Excel –

=XLOOKUP(

STEP 2: Enter the first argument – lookup_value. Here, our lookup value is in cell G1, which contains the Employee ID that we need to look for.

=XLOOKUP(G1,

STEP 3: Enter the second argument – lookup_array. Here, we are going to search in the column containing the list of Employee IDs, i.e., A2:A32

=XLOOKUP(G1,A2:A32,

 

STEP 4: Enter the third argument – return_array. Here, we are going to select the column containing the corresponding values that we want to extract, i.e., D2:D32.

=XLOOKUP(G1,A2:A32,D2:D32)

In this example, we get a match in row 12, where we get a corresponding value of 4/18/2022  from cell D12.

 

Example 2 – Approximate match

We have a table showing the distribution of increments based on the salary of the employee. In this case, the values represent the lower limit of our salary blocks. So we are doing an exact or lower match so we get the accurate result for our employee’s salary.

Let us understand the syntax here:

STEP 1: Enter XLOOKUP in Excel.

=XLOOKUP(

STEP 2: Enter the first argument – Lookup_value. Here our lookup value is in cell B7, which contains the salary of the employee whose increment we want to find out.

=XLOOKUP(B7,

STEP 3: Enter the second argument – Lookup_array. Here we are going to search in the column containing the list of salary blocks, i.e., A2:A5

=XLOOKUP(B7,A2:A5,

STEP 4: Enter the third argument – Return_array. Here we are going to select the column containing the corresponding values that we want to extract, i.e., B2:B5

=XLOOKUP(B7,A2:A5,B2:B5,

STEP 5: Enter the fourth argument – [if_not_found]. This is left blank. So in case of no matches, the function will not return #N/A.

=XLOOKUP(B7,A2:A5,B2:B5,,

STEP 5: Enter the fifth argument – [match_mode]: We are searching for an exact match or the next smaller item, so we enter -1.

=XLOOKUP(B7,A2:A5,B2:B5,,-1)

The salary mentioned in cell B7 is $128,505. Since this value is greater than $100,00 but less than $150,00 the increment value is 9.2%.

 

Example 3 – If not found argument

In this example, we want to return the message “Name not found”, if an employee name is not present in the lookup array.

STEP 1: Enter the first argument – Lookup_value: Here our lookup value is in cell G1, which contains the first name of the employee whose salary we want to find out

=XLOOKUP(G1,

STEP 2: Enter the second argument – Lookup_array: Here we are going to search in the column containing the list of first names, i.e., B2:B32

=XLOOKUP(G1,B2:B32

,

STEP 3: Enter the third argument – Return_array: Here we are going to select the column containing the corresponding values that we want to extract, i.e., D2:D32

=XLOOKUP(G1,B2:B32,D2:D32,

STEP 4: Enter the fourth argument – [if_not_found]: In case of no matches, the XLOOKUP in Excel will return the text – Name not found

=XLOOKUP(G1,B2:B32,D2:D32,”Name not found”)

Here we do not have any employee with the first name Mason, hence the function is returning “Name not found” due to no matches found.

 

Example 4 – Multiple spill values

In this example, we have an employee ID mentioned in cell G1. We want to extract first name, last name, and salary based on the employee ID.

Let us understand the syntax here:

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is in cell A2, which contains the employee id whose details we want to find out.

=XLOOKUP(A2,

STEP 2: Enter the second argument – Lookup_array. Here we are going to search in the column containing the list of employee id, i.e., A5:A35,

=XLOOKUP(A2,A5:A35,

STEP 3: Enter the third argument – Return_array: Here we are going to select the columns containing the corresponding details such as first name, last name, and salary that we want to extract, i.e., B5:D35

=XLOOKUP(A2,A5:A35,B5:D35)

Here we have our employee id of 1010, which spills its corresponding values of the first name, last name, and salary. This is happening because we have selected 3 columns in the return_array argument.

 

Example 5 – Grid lookup

We can perform a Grid lookup by using nested XLOOKUP in Excel, i.e., using an XLOOKUP function inside another. As Excel works by solving the inner function first, the inner XLOOKUP function will be solved first.

In this example, we are trying to get the rate for the category mentioned in cell B8 and the quality mentioned in cell B9. The inner XLOOKUP in Excel will be used to get the rate for the quality mentioned in cell B9 and then using the resultant array the rate for the category mentioned in cell B8 we will get the desired rate.

Let us understand the syntax of the inner XLOOKUP in Excel:

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is in cell B9, which contains the Quality of the product that we want.

=XLOOKUP(B9,

STEP 2: Enter the second argument – Lookup_array: Here we are going to search in the column containing the list of different quality levels offered, i.e., B1:E1

=XLOOKUP(B9,B1:E1,

STEP 3:Enter the third argument – Return_array. Here we are going to select the entire array of prices, i.e., B2:E4

=XLOOKUP(B9,B1:E1,B2:E4)

This XLOOKUP function will solve to give a spilled result of all the prices in column D, as we have observed earlier in our example. This result (D2:D4) is fed as result_array for the outer XLOOKUP in Excel. Let us understand the syntax here:

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is in cell B8, which contains the Category of the product that we want.

=XLOOKUP(B8,

STEP 2: Enter the second argument – Lookup_array. Here we are going to search in the column containing the list of different categories of products offered, i.e., A2:A4

=XLOOKUP(B8,A2:A4,

STEP 3:Enter the third argument – Return_array. Here we will be adding the inner XLOOKUP in Excel.

=XLOOKUP(B8,A2:A4,XLOOKUP(B9,B1:E1,B2:E4))

This XLOOKUP in Excel matches the category of product desired and gives the corresponding price. The end result is the price of the category of product desired with the specified quality level.

 

Example 6 – Using Wildcards

The three wildcards in Excel are:

1. Asterisk mark (*) represents any number of characters. For example, Jo* could mean Joanne, John, Joe, etc.
2. Question mark (?) represents one character. For example, Jo?n could mean John, Joan, etc.
3. Tilde (~) can be used when you want the asterisk or question mark to not be a wildcard. Simply place a tilde just before * or ?.

We can use the wild card feature of Excel to search for specific values. Here we are trying to extract the salary of an employee whose first name starts with J and is a five-letter word. So we are getting the salary of Josan Acuname (Row 5 – Cell D5).

Let us understand the syntax here:

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is in cell G1, which contains the value that we want to search for using wildcards.

=XLOOKUP(G1,

STEP 2:Enter the first argument – Lookup_array: Here we are going to search in the column containing the list of first names of the employees, i.e., B2:B32

=XLOOKUP(G1,B2:B32,

STEP 3: Enter the third argument –Return_array. Here we are going to select the entire array of salaries, i.e., D2:D32

=XLOOKUP(G1,B2:B32,D2:D32,

STEP 4: Enter the fourth argument – [if_not_found]. Left blank, so in case of no matches, the function will not return #N/A

=XLOOKUP(G1,B2:B32,D2:D32,,

STEP 5: Enter the fifth argument – [match_mode]. We are searching for a wildcard match, so we need to enter 2.

=XLOOKUP(G1

,B2:B32,D2:D32,,2)

Here, XLOOKUP in Excel has extracted the salary of the person with a first name starting with “J” and is a five-letter word.

 

Example 7 – Search from Bottom to Up

In this example, we will search the value from bottom to top instead of the default direction (top to bottom). This can be achieved by using the 6th argument of the function – [search_mode].

It is an amazing feature if you wish to find the last occurrence of something in your data. Let’s dive into this XLOOKUP example to learn how.

In this example, we are trying to extract the salary of the person whose first name is John but we will be starting the search from the bottom of the array.

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is in cell G1

=XLOOKUP(G1,

STEP 2: Enter the second argument – Lookup_array. Here we are going to search in the column containing the list of first names of the employees, i.e., B2:B32

=XLOOKUP(G1,B2:B32,

STEP 3: Enter the third argument – Return_array. Here we are going to select the entire array of salaries, i.e., D2:D32

=XLOOKUP(G1,B2:B32,D2:D32,

STEP 4: Enter the fourth argument – [if_not_found]. You can leave this blank.

=XLOOKUP(G1,B2:B32,D2:D32,,

STEP 5: Enter the fifth argument – [match_mode]. You can leave this blank.

=XLOOKUP(G1,B2:B32,D2:D32,,,

STEP 6: Enter the sixth argument – [search_mode]. This should be -1 as we want to search from bottom to top.

=XLOOKUP(G1,B2:B32,D2:D32,,,-1)

You can see that we have 2 people with the first name “John”. Since the search mode was from bottom to up, we got the salary of John Didn’t and not John Acuname.

 

Example 8 – XLOOKUP to the left

One major drawback of VLOOKUP was that it compulsorily had to keep the lookup array in the leftmost column of our dataset for it to be able to search for any criteria. Since XLOOKUP in Excel has separate arrays for both lookup and result, we can bypass this constraint and search right to left as well.

Let us understand the syntax here:

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is in cell G1, which contains the employee ID.

=XLOOKUP(G1,

STEP 2: Enter the second argument – Lookup_array. Here we are going to search in the column containing the list of employee id, i.e., D2:D32

=XLOOKUP(G1,D2:D32,

STEP 3: Enter the third argument – Return_array. Here we are going to select the entire array of corresponding date of joining of employees, i.e., C2:C32

=XLOOKUP(G1,D2:D32,C2:C32)

The salary for the mentioned employee ID has been returned by the XLOOKUP function in Excel even though the return array is on left of the lookup array.

 

Example 9 – Horizontal Lookup

The syntax for performing a horizontal lookup using XLOOKUP in Excel is the same as for a vertical lookup. You just need to provide Excel with the lookup and return an array, the table’s orientation is irrelevant to the XLOOKUP in Excel.

In this example, we are trying to extract the name of the person based on their employee ID. As we can see, the names are in a row and the data normally would have to be transposed to use VLOOKUP. But here we will use XLOOKUP as it can even search values across rows as well as columns.

This eliminates the need for HLOOKUP as well.

Let us understand the syntax here:

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is in cell B4, which contains the Employee ID we want to search for.

=XLOOKUP(B4,

STEP 2: Enter the second argument – Lookup_array. Here we are going to search in the column containing the list of employee IDs, i.e., B1:W1

=XLOOKUP(B4,B1:W1,

STEP 3: Enter the third argument – Return_array. Here we are going to select the entire array containing names, i.e., B2:W2

=XLOOKUP(B4,B1:W1,B2:W2)

Here, we can see that even though the data is in a horizontal format, XLOOKUP in Excel was able to easily extract the required data.

 

Example 10 – Case sensitive XLOOKUP

The XLOOKUP in Excel is not case sensitive i.e. it treats both uppercase and lowercase as the same words. You can make XLOOKUP case-sensitive by using the EXACT Formula in Excel along with XLOOKUP.

The syntax of the EXACT function is –

=EXACT(text1, text2)

What it means:

=EXACT(first text to be compared, second text to be compared)

In this example, we are trying to exact the price for the quality mentioned in cell E1 using the list of quality and prices mentioned in the array A2:B7.

Let us follow the step-by-step tutorial below –

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is TRUE, we are searching for an exact case-sensitive match. This function here works in tandem with the EXACT function.

=XLOOKUP(TRUE,

STEP 2: Enter the second argument – Lookup_array. Here we are using the EXACT function to compare the value in E1 (which contains the Quality level) to each cell in the range A2:A7 (list of all levels of quality offered). The EXACT function returns TRUE if the two numbers are exactly the same, and FALSE otherwise.

=XLOOKUP(TRUE,EXACT(E1,A2:A7)

STEP 3: Enter the third argument –Return_array. Here we are going to select the entire array of the corresponding price of the product at different quality levels, i.e., B2:B7

=XLOOKUP(TRUE,EXACT(E1,A2:A7),B2:B7)

In this case, the XLOOKUP function searches for the first TRUE value in the array that the EXACT function has returned. If it discovers a TRUE result, it returns the value from the range B2:B7.

We can see that Excel has extracted the price of Aa1 i.e. $49.14 and not the price of quality AA1. Hence, allowing lookup to find case-sensitive values.

 

Example 11 – Multiple Criteria in XLOOKUP

In this example, we will be searching for a value that matches more than 1 criteria using XLOOKUP.

Here, we are trying to get data for a product that matches the following three conditions –

• Texture is Silk
• Quality is S1
• Type is Top

Let us follow the steps below to understand how this can be accomplished.

STEP 1: Enter the first argument – Lookup_value. Here our lookup value is 1. This is because test arrays will return 1 only when all the criteria are met.

=XLOOKUP(1,

STEP 2: Enter the second argument – Lookup_array. Here we are checking multiple criteria. We will call them our test arrays.

• First, we want to extract details for a Silk product. We are searching for matches of “Silk” in the entire array B2:B25.
• Then we want a product of the S1 quality category. We are searching for matches of “S1” in the entire array C2:C25
• Finally, we want the details of a Top product. We are searching for matches of “Top” in the entire array D2:D25.

=XLOOKUP(1,(B2:B25=”Silk”)*(C2:C25=”S1″)*(D2:D25=”Top”)

STEP 3: Enter the third argument – Return_array. Here we are going to select the entire array because we want to extract the entire details of the product, i.e., A2:E25.

=XLOOKUP(1,(B2:B25=”Silk”)*(C2:C25=”S1″)*(D2:D25=”Top”),A2:E25)

Product ID 17572 matches all three conditions and thus values related to this product ID have been extracted here.

Conclusion

In conclusion, anyone who frequently uses Excel needs to have access to the XLOOKUP function. It is an invaluable tool for data analysis and manipulation due to its sophisticated search and match capabilities as well as its speed, accuracy, flexibility, and simplicity.

Gaining proficiency with the XLOOKUP in Excel will help you be more productive, save time and effort, and give you a competitive edge at work. So, if you haven’t already, it’s time to begin exploring the power of the XLOOKUP function and unleashing the full potential of Microsoft Excel.