# XLOOKUP with Multiple Criteria in Excel (4 Easy Ways) Whenever someone may need to lookup 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 lookup values depending on multiple criteria. In this article, I’m going to explain XLOOKUP with multiple criteria in Excel.

To make it more visible I’m going to use a dataset of employee information of different departments. There are 3 columns in the dataset which are Employee Name, Dept, and Salary. Here these columns represent the salary information of an employee. ## 4 Ways to do XLOOKUP with Multiple Criteria

### 1. XLOOKUP with Multiple Criteria

You can use the XLOOKUP function with multiple criteria.

To use the XLOOKUP function first select the cell where you want to place your lookup value.
➤ Here, I selected the cell H4
Then, type the following formula in the selected cell or into the Formula Bar.

`=XLOOKUP(F4&G4,\$B\$4:\$B\$14&\$C\$4:\$C\$14,\$D\$4:\$D\$14)` I want to look up at the Salary of Ahmed who works in the IT department. In the function selected the lookup_value F4 & G4, next selected the lookup_array `B4:B14 & C4:C14 `then selected the return_array D4:D14. Finally, it will return the Salary.

Finally, press the ENTER key.
Now, it will show the Salary of the given lookup_value. Later, you can use the Fill Handle to AutoFill the formula for the rest of the cells of the Salary column. #### An Alternate Way

First, select the cell where you want to place your resultant value.
➤ Here, I selected the cell H4
Then, type the following formula in the selected cell or into the Formula Bar.

`=XLOOKUP(1,(\$B\$5:\$B\$15=F4)*(\$C\$5:\$C\$15=G4),\$D\$5:\$D\$15)` Here, to lookup the Salary of Jim who works at Accountant department. In the function given the lookup_value 1, next selected the lookup_array `B5:B14=F4 * C4:C14=G4 `then selected the return_array D4:D14. Finally, it will return the Salary.

Press the ENTER key, 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. ### 2. Two-dimensional/Nested XLOOKUP

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

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

`=XLOOKUP(H4,\$B\$4:\$B\$7,XLOOKUP(I4,\$C\$3:\$F\$3,\$C\$4:\$F\$7))` Here, I want to look up the Salary of Ahmed who works in the IT department. In the function given the lookup_value H4 and selected the lookup_array B5:B7 then again used the XLOOKUP function and selected the 2nd criteria I4 and the lookup_array C3:F3 with the return_array C4:F7. Inconclusively, it will return the Salary.

In the end, press the ENTER key.
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 column. #### An Alternative to Nested XLOOKUP

You can use the nested XLOOKUP formula in another way.

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

`=XLOOKUP(I8, C3:F3, XLOOKUP(H8, B4:B7, C4:F7))` Here, I just interchanged the lookup values from the inner to outer XLOOKUP function.

Finally, press the ENTER key.
Instantly, it will show the Salary of the given lookup_value. ### 3. Complex Multiple Criteria

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

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

`=XLOOKUP(1,(LEFT(B4:B14)="A")*(C4:C14="IT"),B4:D14)` Here, for the different criteria used separate logical expressions. In the LEFT function given the lookup_value “A” with the selected range B4:B14. For the second criteria used “=” operator with lookup_value within the selected range C4:C14. Then I selected the range B4:D14 as return_array. Finally, it will return the Salary with Employee Name and Dept.

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. ### 4. Logical Criteria

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

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

`=XLOOKUP(1,(C4:C14="IT")*(D4:D14>3000),B4:B14)` Here, for multiple logical, 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 C4:C14. For the second criteria used “>” operator with lookup_value within the selected range D4:D14. Then I selected the range B4:D14 as return_array. Finally, it will return the Salary greater than 3000.

Now, press the ENTER key.
In the meantime, it will show the Salary of the given lookup_value. In short, you can use the Fill Handle to AutoFill the formula for the rest of the cells of the Salary column. ## Practice Section

I’ve given a practice sheet in the workbook to practice these explained ways of XLOOKUP with multiple criteria. You can download it from the above. ## In 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.  