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 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 when sales are > $9000.
If you are using single criteria, apply TRUE as a lookup value.
=XLOOKUP(TRUE, D3:D11>9000, B3:B11)
Result:
