XLOOKUP with multiple criteria

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:

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.