XLOOKUP with Multiple Criteria in Excel (4 Easy Ways)

XLOOKUP with Multiple Criteria

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.

Sample Dataset

Download to Practice

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)

XLOOKUP with Multiple Criteria

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.

XLOOKUP with Multiple Criteria

Later, you can use the Fill Handle to AutoFill the formula for the rest of the cells of the Salary column.

XLOOKUP with Multiple Criteria

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)

Alternate way to XLOOKUP with Multiple Criteria

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.

Alternate way to XLOOKUP with Multiple Criteria

Now, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Salary column.

Alternate way to XLOOKUP with Multiple Criteria

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))

Nested XLOOKUP

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.

Nested XLOOKUP

Here, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Salary column.

Nested XLOOKUP

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))

Nested XLOOKUP (Another Way)

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.

Nested XLOOKUP (Another Way)

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)

Complex Multiple Criteria

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.

Complex Multiple Criteria

Therefore, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Salary column.

Complex Multiple Criteria

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)

Logical Criteria

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.

Using Logical Criteria
In short, you can use the Fill Handle to AutoFill the formula for the rest of the cells of the Salary column.

Using Logical Criteria

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.

Practice Sheet

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.

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo