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 which 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 corresponding 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 single logical criteria.

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

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

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

Result: