# How to Use XLOOKUP Function with Multiple Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

Whenever someone may need to look up values depending on multiple criteria from a large worksheet then you can use some functions to do it. XLOOKUP is one of the functions to look up values depending on multiple criteria. In this article, I’m going to explain XLOOKUP with multiple criteria in Excel.

In the following picture, you can see the overview of XLOOKUP with multiple criteria. Let’s dive into the following article to do the task. The following dataset has the Employee Name, Dept, and Salary columns. After that, using this dataset, we will go through 4 easy methods to use XLOOKUP with multiple criteria. Here, we used Excel 365. You can use any available Excel version. ## 1. Using Only XLOOKUP Function with Multiple Criteria in Excel

You can use the XLOOKUP function with multiple criteria for a single time.

### 1.1. Using Ampersand Operator to Set Multiple Criteria

Here, we will set criteria using the ampersand operator in the XLOOKUP function.

Steps:

• First of all, I selected cell D18.
• Then, type the following formula in the selected cell or into the Formula Bar.

`=XLOOKUP(B18&C18,\$B\$5:\$B\$15&\$C\$5:\$C\$15,\$D\$5:\$D\$15)` Formula Breakdown

• I want to look up the Salary of Ahmed who works in the IT department.
• In the function selected the lookup_value F4 & G4.
• Next selected the lookup_array B5:B15 & C5:C15.
• Then selected the return_array D5:D15.
• Finally, it will return the result in cell D18.
• Finally, press ENTER.
• Therefore, it will show the Salary of the given lookup_value. • Afterward, you can use the Fill Handle to AutoFill the formula for the rest of the cells of the Salary. ### 1.2. Set Criteria for Multiple Columns Separately

Here, we will set criteria for multiple columns separately inside multiple brackets in the XLOOKUP function. Actually, this procedure is similar but an alternate way of 1.1.

Steps:

First, select the cell where you want to place your resultant value.

• Here, I selected cell D18.
• Then, type the following formula in the selected cell or into the Formula Bar.

`=XLOOKUP(1,(\$B\$5:\$B\$15=B18)*(\$C\$5:\$C\$15=C18),\$D\$5:\$D\$15)` Formula Breakdown

• Here, to look up the Salary of Jim who works at Accountant department.
• In the function given the lookup_value 1.
• Next selected the lookup_array B5:B15=B18 * C5:C15=C18.
• Then selected the return_array D5:D15.
• Finally, it will return the result in cell D18.
• Press the ENTER key, and eventually, it will show the Salary of the given lookup_value.
• Now, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Salary column. • As a result, you can see the complete Salary. ## 2. Combining Two XLOOKUP Functions for Multiple Criteria in Excel

Here you can use the XLOOKUP function in a two-dimensional or nested way to look up values.

### 2.1. Using XLOOKUP Function in Row-Wise Direction and then in Column-Wise Direction

Here, we will employ two XLOOKUP functions in row-wise and column-wise directions.

Steps:

• First, select the cell to place your resultant value.
• Here, I selected cell D11.
• Then, type the following formula in the selected cell or into the Formula Bar.

`=XLOOKUP(B11,\$B\$5:\$B\$8,XLOOKUP(C11,\$C\$4:\$F\$4,\$C\$5:\$F\$8))` Formula Breakdown

• Here, I want to look up the Salary of Ahmed who works in IT.
• In the function given the lookup_value B11 and selected the lookup_array B5:B8.
• The outer XLOOKUP function searches in the column-wise direction.
• Then, again used the XLOOKUP function and selected the 2nd criterion C11.
• The inner XLOOKUP function searches in the row-wise direction.
• The lookup_array C4:F4 with the return_array C4:F8.
• Finally, it will return the result in cell D18.
• In the end, press ENTER.
• Then, it will show the Salary of the given lookup_value.
• Here, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Salary. • Therefore, you can see the complete Salary column. ### 2.2. Applying XLOOKUP Function in Column-Wise Direction and then in Row-Wise Direction

You can use the nested XLOOKUP formula in an alternate way from the previous section.

Steps:

• First, select the cell to place your resultant value.
• Here, I selected cell D11.
• Then, type the following formula in the selected cell or into the Formula Bar.

`=XLOOKUP(C11, \$C\$4:\$F\$4, XLOOKUP(B11, \$B\$5:\$B\$8, \$C\$5:\$F\$8))` Formula Breakdown

• Here, I just interchanged the lookup values from the inner to outer XLOOKUP function.
• The outer XLOOKUP function searches the value in a column-wise direction.
• The inner XLOOKUP function searches the in a row-wise direction.
• Finally, press ENTER.
• Instantly, it will show the Salary of the given lookup_value.
• Furthermore, we will drag down the formula with the Fill Handle tool. • Therefore, you can see the complete Salary column. ## 3. Applying XLOOKUP Function with Advanced Multiple Criteria in Excel

You can also lookup values depending on multiple complex criteria using the XLOOKUP function along with the LEFT function.

Steps:

• To begin with, select the cell to place your resultant value.
• Here, I selected cell B18.
• Then, type the following formula in the selected cell or into the Formula Bar.

=XLOOKUP(1,(LEFT(B5:B15)=”A”)*(C5:C15=”IT”),B5:D15) Formula Breakdown

• In the LEFT function for the lookup_value “A”, searches within the selected range B5:B15.
• For the 2nd criterion, we used the “=” with lookup_value inside the selected range C5:C15.
• Then I selected the range B5:D15 as return_array.
• Finally, it will return the result.
• Press the ENTER key and it will show the Salary of the given lookup_value.
• Therefore, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Salary column. • Then, you can see the result. ## 4. Inserting XLOOKUP Function with Multiple Logical Criteria in Excel

You can also use the XLOOKUP function to look up values depending on multiple logical criteria.

Steps:

• To begin with, select the cell to place your resultant value.
• Here, I selected cell F4.
• Then, type the following formula in the selected cell or into the Formula Bar.

`=XLOOKUP(1,(C5:C15="IT")*(D5:D15>3000),B5:B15)` Formula Breakdown

• Here, for multiple logic, I will use boolean logic, and then it will look for the number.
• In the XLOOKUP function given the lookup_value “1” with the selected range C5:C15. For the second criterion used “>” operator with lookup_value within the selected range D5:D15.
• Then I selected the range B5:B15 as return_array.
• Finally, it will return the result in cell B18.
• Now, press ENTER.
• In the meantime, it will show the Salary of the given lookup_value. • Furthermore, we type the following formula for cell B19.

`=XLOOKUP(1,(C5:C15="Sales")*(D5:D15>1500),B5:B15)`

• After that, press ENTER.
• Therefore, you can see the result in cell B19. ## Conclusion

In this article, I tried to explain 4 easy and quick ways of XLOOKUP with multiple criteria in Excel. These different ways will help you to perform XLOOKUP with multiple criteria. Last but not least if you have any kind of suggestions, ideas, and feedback please feel free to comment down below. You can visit our website Exceldemy to explore more. Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  