XLOOKUP with multiple criteria

Use XLOOKUP with multiple criteria through concatenating lookup values and lookup arrays inside the formula. Today’s guide will use the XLOOKUP function to look up values based on multiple criteria.

Take a look at the example below! We will use the basic syntax of the function without optional arguments:

=XLOOKUP(lookup value, lookup array, return array)

Because we want to use multiple criteria, we concatenate the arguments.

=XLOOKUP(K3&K4&K5, D3:D11&E3:E11&F3:F11, G3:G11) =$20

Explanation: Lookup values using multiple criteria

In the example, our goal is to find the item’s price, where the item is apple, the quality is excellent, and the price is discounted.

Using VLOOKUP, we can’t use arrays inside the formula. With XLOOKUP, we’ll construct arrays in the formula using them as arguments.

=XLOOKUP(value1&value2&value3, range1&range2&range3, results)

To create a single argument from the multiple criteria, construct the lookup_value (the first argument of XLOOKUP function) by using the “&” sign.

construct an array using xlookup

Let us see the arguments that contain multiple criteria:

  • lookup value: K3&K4&K5
  • lookup_array: D3:D11&E3:E11&F3:F11
  • return_array: G3:G11
lookup array with multiple criteria

In the example, the formula in K6:

=XLOOKUP(K3&K4&K5, D3:D11&E3:E11&F3:F11, G3:G11) = $20

XLOOKUP returns $20, the price for a discounted but excellent quality apple.

Note: In the case of multiple criteria, Excel evaluates the formula using the following logic:

  • lookup_value: “AppleExcellentYes

Tip: XLOOKUP has native support to arrays. You don’t need to apply the Ctrl + Shift + Enter command.

If you want to take a closer look at the demonstrated example, download the practice file!

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.