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.
Read More: VLOOKUP with Multiple Criteria Including Date Range in Excel (2 Ways)
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.
Read More: Vlookup with Multiple Criteria without a Helper Column in Excel (5 Ways)
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.
Read More: How to Lookup Across Multiple Sheets in Excel (3 Methods)
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.
Read More: Lookup and Return Multiple Values Concatenated into One Cell in Excel
Download Practice Workbook
You can download the Excel file from the link below.
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.
