Summary

To filter data to include only records where a value is this or that, you can use the FILTER function and simple boolean logic expressions. In the example shown, the formula in F5 is:

=FILTER(B5:D14,(D5:D14="red")+(D5:D14="blue"),"No results")

The result returned by FILTER includes only rows where group is "red" or "blue".

Generic formula

=FILTER(rng1,(rng2="red")+(rng2="blue"),"No results")

Explanation 

This formula relies on the FILTER function to retrieve data based on a logical test built with simple expressions and boolean logic:

(D5:D14="red")+(D5:D14="blue")

After each expression is evaluated, we have the following two arrays:

({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE})+
({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE})

The math operation (addition) coerces TRUE and FALSE values to 1s and 0s:

=({1;0;0;0;0;0;1;0;0;0})+({0;1;0;0;1;0;0;1;0;0})

The result is a single array like this:

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

This final array is delivered to the FILTER function as the include argument, and FILTER returns only rows that correspond to a 1. Or, to put it another way, FILTER removes rows that are zero.

Not mutually exclusive

In the example above, we are testing for two possible values in a single column of data. This means that the two tests are mutually exclusive — both tests can't return TRUE at the same time. However, if you are testing multiple columns/fields for values, there is the possibility that more than one logical test will return TRUE. In that case, the final array may contain numbers larger than 1 (i.e. TRUE + TRUE = 2). This makes a difference in some formulas. However, in this case, it doesn't matter, because FILTER will treat any non-zero value as TRUE when evaluating the include argument.

Video: Boolean algebra in Excel formulas

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.