XLOOKUP with multiple criteria
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.
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:
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:
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.