XLOOKUP with logical criteria

use-xlookup-with-logical-criteria

Learn how to use XLOOKUP with logical criteria to build useful formulas (expressions) based on boolean logic.

XLOOKUP has a great advantage: the native arrays support.

Generic Formula to use XLOOKUP with logical criteria

=XLOOKUP(1, (range1=value1) * (range2>value2), results)

In the example, we want to find the first order ID that meets the following requirements:

  • location (in column C) is equal to “New York”
  • sales (in column D) is greater than 8000

The relationship between the two logical criteria is AND.

multiple-logical-criteria-data-set

Type the following formula in cell G3:

=XLOOKUP(1,(C3:C11=”New York”)*(D3:D11>8000),B3:B11)

The formula returns the first order date (5-Jan-2022), where the Location = “New York” AND the sales are greater than 8000.

xlookup-result-array-example

Using XLOOKUP, you can build criteria-based arrays using the function arguments and apply multiple logical tests.

We are building a lookup array (the second argument of XLOOKUP) using boolean logic:

(C3:C11=”New York”) * (D3:D11>8000)

How to calculate the product of two arrays

Working with logical operators in Excel is not rocket science. In case of multiple logical criteria, Excel evaluates the formula and returns two arrays that contain TRUE / FALSE values:

  • the result of logical test in range C3:C11 = {FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}
  • the result of logical test in range D3:D11 = {FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE}

We will display these parts of the calculations for better understanding:

How-to-calculate-product-of-two-arrays-using-logical-criteria

The result is a single array that contains 1’s and 0’s:

{0;0;0;0;0;0;0;1;0;0}

We will use the result as a lookup array. In the next formula, use 1 as a lookup value:

=XLOOKUP(1,{0;0;0;0;0;0;0;1;0;0}, B3:B11)

The XLOOKUP-based formula lookup and matches the ‘1’ in the 6th position. Finally, it returns the 6th value from range B3:B11, “5-Jan-2022”.

Using the earlier method, you can use logical criteria inside the formula.

Single criteria

Math operations automatically force TRUE values to 1 and FALSE values to 0. In the example, we have a single logical criteria.

We are looking for the order date when sales are > $9000.

If you are using single criteria, apply TRUE as a lookup value.

=XLOOKUP(TRUE, D3:D11>9000, B3:B11)

Result:

single-logical-criteria

Related Formulas and Resources: