Learn how to use the XLOOKUP function with Boolean expression (OR logic). To do that set the lookup value to 1.
XLOOKUP is our favorite dynamic array function, and you can use it in various situations.
Generic formula to use a boolean or logic
In the example, our goal is to find and list all records where the color is “orange” or “blue”.
Formula:
=XLOOKUP(1 ,boolean_expression, data)
The arguments of the XLOOKUP function:
- lookup value is 1; in other words, TRUE.
- lookup_array is an expression, and it has two possible outputs (TRUE or FALSE) based on the boolean logic
- the lookup array is named range, “sales_table”, (B3:D10)
Note: sales_data is a named range and referred to as range B3:D10.
Formula:
=XLOOKUP(1, (C3:C10=”blue”)+(C3:C10=”orange”), sales_data)
Good to know when you use boolean logic; Excel follows the rules below:
- Multiplication (*) corresponds to AND operator
- Addition (+) corresponds to OR operator
XLOOKUP with Boolean OR logic
Working from the inside out, we will evaluate the following expressions:
=C3:C10 = “blue”
=C3:C10 = “orange”
Take a look at the first array in column F. If the color is “blue” in the given row in range C3:C10, the expression returns with TRUE (1), else FALSE (0).
The =C3:C10 = “orange” expression gets TRUE (1) if the cell value is “orange”, else gets FALSE (0).
We will apply math operators to convert the TRUE and FALSE outputs to 1 or 0. But, first, create a helper column. Use the “+” sign to create the lookup_array for the second argument of XLOOKUP.
Evaluate the =(C3:C10=”blue”)+(C3:C10=”orange”) section:
The result is 1 if the cell contains the “blue” OR “orange” value. Now have the lookup array for the second argument of XLOOKUP.
The formula looks like this:
=XLOOKUP(1, {0,0,0,1,1,1,0,1}, sales_data)
Evaluate the formula:
The formula will find the first matching record that meets the boolean or logic criteria. In this case, the first matching record has in row 4.
Workaround with FILTER function
If you want to get all matching records, use the FILTER function.
Formula:
=FILTER(sales_table, (C3:C10=”blue”)+(C3:C10=”orange”))
Result:
