Excel formula: XLOOKUP with multiple criteria

Generic formula

=XLOOKUP(val1&val2&val3,rng1&rng2&rng3,results)

Summary

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.

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.

Explanation

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.

Dynamic Array Formulas are available in Office 365 only.

Author

Dave Bruns

Related formulas

XLOOKUP with logical criteria

XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions. In the example shown, we are looking for the order number of the first order to Chicago over $250. We are...

VLOOKUP with multiple criteria

In the example shown, we want to lookup employee department and group using VLOOKUP by matching on first and last name. One limitation of VLOOKUP is that it only handles one condition: the lookup_value, which is matched against the first column in...

XLOOKUP with Boolean OR logic

In this example, the goal is to use XLOOKUP to find the first "red" or "pink" record in the data as shown. All data is in an Excel Table named data in the range B5:E14. This means the formulas below use structured references . As a result, the...

INDEX and MATCH with multiple criteria

This is a more advanced formula. For basics, see How to use INDEX and MATCH . Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria. Without concatenating values...

XLOOKUP with complex multiple criteria

Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use Boolean logic to create a lookup array on-the-fly...

Related functions

Excel XLOOKUP Function

The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal...

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.

500 Formulas | 101 Functions

Exceljet

Quick, clean, and to the point

XLOOKUP with multiple criteria

With boolean logic

Excel Formula Training

Download 100+ Important Excel Functions

Excel video training