< Go Back

Cell equals one of many things

In Excel, you can check to see if a cell in your spreadsheet contains one of the multiple values in another cell. This is done using a formula that utilizes the SUMPRODUCT syntax.

Syntax

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

Explanation

Let’s assume there’s a list of text strings in your Excel spreadsheet, listed in the range A2 to A10. Now, the idea is to test the cells one after the other with respect to a different list in another range, D2 to D6. What we’re doing here is to check if “cell equals one of many things”.

The result of each test returns as either TRUE or FALSE.

This is generated using the generic formula (or syntax) which combines all the values in the text strings and assesses the “Things” section to check if the supplied value exists in each range. If it is, it returns as TRUE. And if it isn’t, it’s FALSE.

Example

Using the problem stated below, here’s how the process of checking if a cell equals one of many things looks like in Excel.

Step 1: Now that we have two ranges for the “text strings” and “things” (can also be referred to as specific text), you can create another range for the results of your test. Tag the column as “Results”.

Figure 1. Excel showing cells containing the text string.

Step 2: For the “Result” range, enter the formula in one of the blank cells. In this case of our example, the formula to use would be, =SUMPRODUCT(--(A2=D2:D6))>0. The first result is displayed in the image below.

Figure 2. SUMPRODUCT function returns the results TRUE.

Step 3: To check if the other cells equal one of many things. Or not. Drag and drop the first “TRUE” box to the last box in the range. It shows you the result for each cell like it is shown in the image below.

Figure 3. SUMPRODUCT returns TRUE or FALSE values of each cell.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar