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)

Construct the lookup_value (the first argument of the XLOOKUP function) using the “&” sign to create a single argument from the multiple criteria.

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 for 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 Visual Analytics. He helps people reach the top in Excel.