XLOOKUP with multiple criteria
=XLOOKUP(val1&val2&val3,rng1&rng2&rng3,results)
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.
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.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
