Use XLOOKUP with Multiple Criteria in Excel (Formula)

If you want to perform a lookup using multiple criteria, you can use XLOOKUP. And in XLOOKUP, there are two ways to do that. In this tutorial, we will look at and understand both methods in detail.

In the below example, we have a list of students with roll numbers, names, and scores.

Multiple Criteria with XLOOKUP (Helper Column Method)

1. First, add a new column to use as the helper column.
   
2. Now, in the helper column, you need to combine the roll number and name of the student.
   
3. After that, create the concatenated value to lookup. Then, in two cells, enter the roll number and name of the student.
   
4. Next, enter the XLOOKUP and combine values from the call F2 and G2 to use as the lookup value in the function.
   
5. From here, refer to the helper column as lookup_array and the score column as a return_array.
   
6. In the end, hit enter to get the result.
   

You can use the other optional arguments in XLOOKUP if you want.

Multiple Criteria with XLOOKUP (Array Method)

You can use an array if you don’t want to use the helper column. First, let’s write this formula and then learn how it works.

1. Enter the XLOOKUP in a cell, and in the lookup_value, enter 1.
   
2. Now, we need to create an array to look up the roll number in the roll number column and the name in the name column. So, enter (A2:A20=E2)*(B2:B20=F2) in the lookup array.
   
3. After that, in the return_array, refer to the score column.
   
4. In the end, close the function and hit enter to get the result.
   

Arrays are what makes this formula powerful.

Let’s understand this in detail.

In the first part of the array, we tested the name in the name column, and it returned an array with TRUE and FALSE.

You can see we have TRUE in the sixth position in the array, which is the position of the name we are looking up in the name column.

The same thing is in the second array, where we have TRUE for the roll number we look up.

Further, when you multiply both arrays, you get a new array with 0 and 1. In this array, 1 is in the sixth position.

We have lookup_value 1 in the function, and the array in lookup_array 1 is in the sixth position. So that’s why XLOOKUP returns the score from the sixth position.