XLOOKUP with boolean OR logic

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.

Not mutually exclusive

In the example above, we are testing for two possible values in a single column of data. Because the values are mutually exclusive, both tests can't return TRUE at the same time. If you are testing multiple columns/fields for values, or if the tests are otherwise not mutually exclusive, the formula logic needs to be adjusted to handle the possibility that more than one logical test will return TRUE. In that case, when the result arrays are added together, the final array will contain a number larger than 1 and the lookup value of 1 will not be found. To handle this possibility, adjust the formula as follows:

=XLOOKUP(1,--((test1)+(test2)>0),array)

In this version, we add result arrays together and check to see if results are greater than 0. This creates a new array containing only TRUE and FALSE values. The double negative (--) is used to convert the TRUE and FALSE values to 1s and 0s so the lookup value of 1 will continue to work.

For example, in the worksheet shown, to test for a color of "green" or a quantity of 17, use a formula like this:

=XLOOKUP(1,--((data[Color]="green")+(data[Qty]=17)>0),data)

Video: Boolean algebra in Excel.