=SUMPRODUCT(--(A1=things))>0

If you want to test a cell to see if it equals one of several things, you can do so with a formula that uses the SUMPRODUCT function.

Let's say you have a list of text strings in the range B5:B11, and you want to test each cell against another list of things in range E5:E9. In other words, for each cell in B5:B11, you want to know: does this cell equal any of the things in E5:E9?

You could start build a big formula based on nested IF statements, but an array formula based on SUMPRODUCT is a simpler, cleaner approach.

The solution is to to create a formula that will test for multiple values and return a list of TRUE / FALSE values. Once we have that, we can process that list (an array, actually) with SUMPRODUCT.

The formula we're using looks like this:

=SUMPRODUCT(--(B5=things))>0

The key is this snippet:

--(B5=things)

which simply compares the value in B5 to every value in the named range "things". Because we are comparing B5 to an array (i.e. the named range "things", E5:E11) the result will be an array of TRUE / FALSE values like this:

{TRUE;FALSE;FALSE;FALSE;FALSE}

If we have even one TRUE in the array, we know that B5 equals at least one thing in the list, so, to force the TRUE / FALSE values to 1s and 0s, we use a double negative (--, also called a double unary). After this coercion, we have this:

{1;0;0;0;0}

Now we process the result with SUMPRODUCT, which will add up the elements in the array. If we get any non-zero result, we have at least one match, so we use >1 to force a final result of either TRUE or FALSE.

There's no requirement that you use a range for your list of things. If you're only looking for a small number of things, you can use a list in array format, which is called an array constant. For example, if you're just looking for the colors red, blue, and green, you can use {"red","blue","green"} like this:

--(B5={"red","blue","green"})

If the cells you are testing contain any extra space, they won't match properly. To strip all extra space, you can modify the formula to use the TRIM function like so:

=SUMPRODUCT(--(TRIM(A1)=things))>0