# XLOOKUP with multiple criteria

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

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,0,1)

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

*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 handle arrays directly, instead of requiring ranges that come directly from 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 then, 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 is exact, and search mode is 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,0,1)

This is a more flexible approach because the syntax can be adjusted to use other logical operators.

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