=XLOOKUP(val1&val2&val3,rng1&rng2&rng3,results) val = Lookup Value rng = Lookup Array
In order to perform an XLOOKUP with multiple Criteria, you need to concatenate the lookup values and arrays. In our example, this is achieved by using “&” between both the lookup values and arrays.
Xlookup with Multiple Criteria Overview
Let’s look at the below using the XLOOKUP function with multiple criteria. B1&B2&B3 are the lookup Values for our Formula. The are the Sales Person, Territory, and Gender. For each lookup array, we need to specify the corresponding row.
It’s important to make sure you match the same orders as your lookup values. For example, for B1 the correct array is A7-A14. From there, we continue to fill out our XLOOKUP formula.
Finally, we select our result column, D7-D14. This will match the three criteria bring us the correct result.
If you have any questions, please review our Community Forum.