Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Add Outline to Data in Excel
  • Get column name from index in Excel Table
  • Remove Duplicates Example in Excel

References

  • How to get address of named range in Excel
  • How to use Excel COLUMN Function
  • Extract all partial matches in Excel
  • How to use Excel INDIRECT Function
  • Two-column Lookup in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Data validation must not exist in list
  • Excel Data validation unique values only
  • Excel Data validation require unique number
  • Excel Data validation date in next 30 days

How to check cell equals one of many things in Excel

by

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.

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

Formula

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

Explanation

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

How this formula works

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.

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:

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

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:

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

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Return blank if in Excel
  • OR function Examples in Excel
  • How to use Excel FALSE Function
  • SWITCH function example in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get days before a date in Excel
  • Calculate years between dates in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate number of hours between two times in Excel
  • How to get Weekdays, Working days between Two Dates in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Map text to numbers in Excel
  • If cell contains one of many things in Excel
  • Running count group by n size in Excel
  • Categorize text with keywords in Excel

General

  • Count cells less than in Excel
  • Count cells that contain errors in Excel
  • Count cells that do not contain errors in Excel
  • How to calculate project complete percentage in Excel
  • Count cells that do not contain many strings in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning