# XLOOKUP Multiple Criteria

Download the example workbook

*This tutorial will demonstrate how to perform an XLOOKUP with multiple criteria in Excel. If your version of Excel does not support XLOOKUP, read how to use the VLOOKUP instead.*

* *

**XLOOKUP Multiple Criteria Concatenation**

One common way of performing a multiple criteria XLOOKUP is by concatenating all the criteria into one lookup value and their corresponding lookup columns into one lookup array.

`=XLOOKUP(F3&G3,B3:B7&C3:C7,D3:D7)`

Let’s walk through the formula:

**Concatenate the Lookup Columns**

First, we concatenate the lookup columns to create an array of unique IDs, which enables us to look up all the criteria simultaneously.

`=B3&C3`

**Concatenate the Criteria**

Next, we also do the same for the criteria to create a new lookup value.

`=F3&G3`

**XLOOKUP Function**

Finally, we supply the new lookup value and lookup array to the XLOOKUP Function.

`=XLOOKUP(K3,I3:I7,D3:D7)`

Putting it all together yields the original formula:

`=XLOOKUP(F3&G3,B3:B7&C3:C7,D3:D7)`

**XLOOKUP Multiple Criteria Boolean Expressions**

Another option is creating Boolean expressions where the criteria are checked against their corresponding lookup columns.

`=XLOOKUP(1,(B3:B7=F3)*(C3:C7=G3),D3:D7)`

Let’s walk through this formula:

**Boolean Expressions**

First, let’s apply the appropriate criteria to their corresponding columns by using the logical operators (e.g., =,<,>).

Let’s start with the first criterion (e.g., Student ID).

`=B3=$F$3`

Repeat the step for the other criteria (e.g., Subject).

`=C3=$G$3`

**Array AND**

Next, we perform the array equivalent of the AND Function by multiplying the Boolean arrays where TRUE is 1 and FALSE is 0.

`=I3*J3`

Note: The AND Function is an aggregate function (many inputs to one output). Therefore, we can’t use it in array calculations.

**XLOOKUP Function**

Next, we look up 1 from the result of the Array AND.

`=XLOOKUP(1,K3:K7,D3:D7)`

Combining all formulas yields our original formula:

`=XLOOKUP(1,(B3:B7=F3)*(C3:C7=G3),D3:D7)`