Return to Excel Formulas List

XLOOKUP Multiple Criteria

Download Example Workbook

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

 

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 multiple criteria concatenation

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.

Concatenate the Lookup Columns

Concatenate the Criteria

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

Concatenate the criteria

XLOOKUP Function

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

Xlookup function

Putting it all together yields the original formula:

 

XLOOKUP Multiple Criteria Boolean Expressions

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

Xlookup multiple criteria boolean expresion

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

Boolean expressions

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

Boolean expressions condition subject

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.

Array and

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 function expresion

Combining all formulas yields our original formula: