Quick, clean, and to the point

XLOOKUP with multiple criteria

Excel formula: XLOOKUP with multiple criteria
Generic formula 

To use XLOOKUP with multiple criteria, you can concatenate lookup values and lookup arrays directly in the formula. In the example shown, the formula in H8 is:


XLOOKUP returns $17.00, the price for a Large Red T-shirt.

Notes: XLOOKUP can handle arrays natively; there is no need to enter with control + shift + enter. See below for more flexible version of this formula based on Boolean logic.


One of the nice advantages of XLOOKUP over VLOOKUP is that XLOOKUP can work with arrays directly, instead of requiring ranges on a worksheet. This makes it possible to assemble arrays in the formula, and push these into the function.

Working one argument at a time, the lookup value is created by joining H5, H6, and H7 using concatenation:


This results in the string "T-shirtLargeRed".

The lookup array is created in a similar way, except we are now joining ranges:


The return array is supplied as a normal range:, E5:E14:


In essense, we are looking for the lookup value "T-shirtLargeRed" in data like this:

lookup_array result_array
T-shirtSmallRed 15
T-shirtMediumBlue 16
T-shirtLargeRed 17
HoodieSmallGray 28
HoodieMediumBlue 29
HoodieLargeBlack 30
HatMediumBlack 25
HatMediumGray 26
HatLargeRed 24
T-shirtLargeBlue 16

Match mode defaults to exact, and search mode defaults to first match, so XLOOKUP returns $17.00.

With boolean logic

While the syntax explained above works fine for simple "equals to" matching, you can also use boolean logic to construct a formula like this:


This is a more flexible approach because the syntax can be adjusted to use other logical operators and other functions as needed for more complex lookups.

Dynamic Array Formulas are available in Office 365 only.
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.