Exceljet

Quick, clean, and to the point

XLOOKUP with multiple criteria

Excel formula: XLOOKUP with multiple criteria
Generic formula 
=XLOOKUP(val1&val2&val3,rng1&rng2&rng3,results)
Explanation 

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(H5&H6&H7,B5:B14&C5:C14&D5:D14,E5:E14)

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

Note: XLOOKUP can handle arrays natively; there is no need to enter with control + shift + enter.

XLOOKUP is a beta function, available only through the Office Insiders program.

How this formula works

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:

=XLOOKUP(H5&H6&H7

This results in the string "T-shirtLargeRed".

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

=XLOOKUP(H5&H6&H7,B5:B14&C5:C14&D5:D14

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

=XLOOKUP(H5&H6&H7,B5:B14&C5:C14&D5:D14,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:

=XLOOKUP(1,(B5:B14=H5)*(C5:C14=H6)*(D5:D14=H7),E5:E14)

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.

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