Filter this or that
=FILTER(rng1,(rng2="red")+(rng2="blue"),"No results")
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".
How this formula works
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 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 to 1 and zero, and the result is a single array:
=({1;0;0;0;0;0;1;0;0;0})+({0;1;0;0;1;0;0;1;0;0})
Since we are using Boolean algebra, there are only two possible values operation: 1 or 0. Thus, the final array contains only 1's and 0's:
={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.
