Context

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.

Solution

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: 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.

With a hard-coded list

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:

Dealing with extra spaces

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:

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.