Exceljet

Quick, clean, and to the point

XLOOKUP with boolean OR logic

Excel formula: XLOOKUP with boolean OR logic
Generic formula 
=XLOOKUP(1,boolean_expression,data)
Summary 

To configure XLOOKUP with boolean OR logic, use a lookup value of 1 with a logical expression based on addition. In the example shown, the formula in G5 is:

=XLOOKUP(1,(data[Color]="red")+(data[Color]="pink"),data)

where "data" is the name of the Excel Table to the left.

Explanation 

The lookup value is provided as 1, for reasons that become clear below. For the lookup array, we use an expression based on boolean logic:

(data[Color]="red")+(data[Color]="pink")

In the world of boolean Algebra, AND logic corresponds to multiplication (*), and OR logic corresponds to addition (+). Because we want OR logic, we use addition in this case. Notice Excel is not case-sensitive, so we don't need to capitalize the colors.

After the expression is evaluated, we have two arrays of TRUE and FALSE values like this:

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}+
{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

Notice, in the first array, TRUE values correspond to "red". In the second array, TRUE values correspond to "pink".

The math operation of adding these arrays together converts the TRUE and FALSE values to 1s and 0s, and results in a new array composed only of 1s and 0s:

{0;0;1;0;1;0;0;0;0;1}

Notice the 1s in this array correspond to rows where the color is either "red" or "pink".

We can now rewrite the formula as:

=XLOOKUP(1,{0;0;1;0;1;0;0;0;0;1},data)

The first 1 in the lookup array corresponds to row three of the data, where the color is "red". Since XLOOKUP will by default return the first match, and since the entire table "data" is supplied as the return array, XLOOKUP returns the third row as a final result.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.