XLOOKUP with boolean or logic

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

boolean expression lookup formula

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:

return_array TRUE or FALSE

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.

boolean or logic lookup result array

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:

workaround with filter function

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.