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.

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.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.