XLOOKUP with boolean OR logic

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.

How this formula works

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.