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

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.

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:

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:

**Related Formulas and Resources:**