Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use XLOOKUP Function with Multiple Criteria in Excel

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.

overview of XLOOKUP with multiple criteria


Download Practice Workbook

You can download the Excel file from the link below.


4 Examples of Using XLOOKUP Function with Multiple Criteria in Excel

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.

Dataset of XLOOKUP with multiple criteria


1. Inserting Single XLOOKUP Function with Multiple Criteria

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


1.1. Setting Criteria Using Ampersand Operator in XLOOKUP Function

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)

Inserting XLOOKUP with Multiple Criteria

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.

Applying Fill Handle Tool

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

Final Salary Column


1.2. Setting 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)

Setting Criteria for Multiple Columns in XLOOKUP Function

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.

Employing Fill Handle Tool

  • As a result, you can see the complete Salary.

The Complete Salary Column

Read More: VLOOKUP with Multiple Criteria Including Date Range in Excel (2 Ways)


2. Using Two XLOOKUP Functions in Excel

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


2.1. Using Two XLOOKUP Functions in Row-Wise and 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))

Using Two XLOOKUP Function in Row Wise and Column Wise Direction

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.

Use of Fill Handle Feature

  • Therefore, you can see the complete Salary column.

Final Salary Column


2.2. Using Two XLOOKUP Functions in Column-Wise and 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))

Using Two XLOOKUP Functions in Column-wise and Row-wise Direction

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.

Dragging Down Formula Using Fill Handle Tool

  • Therefore, you can see the complete Salary column.

The Complete Salary Column

Read More: Vlookup with Multiple Criteria without a Helper Column in Excel (5 Ways)


3. Use of XLOOKUP Function with Complex Multiple Criteria

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)

Use of XLOOKUP with Complex Multiple Criteria

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.

Employing Fill Handle Feature

  • Then, you can see the result.

The Output after Using Formula

Read More: How to Lookup Across Multiple Sheets in Excel (3 Methods)


4. Applying XLOOKUP Function with Logical Multiple Criteria

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)

Applying XLOOKUP with Logical Multiple Criteria

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.

The Value after using Formula

  • 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.

The final salary column

Read More: Lookup and Return Multiple Values Concatenated into One Cell in Excel


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 Section


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.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. 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. 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

Leave a reply

ExcelDemy
Logo