ThisCalculator

Everyday

If Cell Contains One of Many Things? Excel Formula

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This article explains how to check if a cell contains any one of several specified values or substrings in Excel and Google Sheets, and what formula to use. You enter the cell to test and the list of things to look for, and the formula returns TRUE if any match is found, otherwise FALSE.

Formula

The standard formula to test if a cell contains any of several substrings is:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(list, t)))>0, "Yes", "No")

Where:

  • t – the cell containing the text you want to test (e.g., A2).
  • list – a range of cells or an array constant containing the substrings you are looking for (e.g., {“apple”,”banana”,”cherry”}).
  • SEARCH – finds a substring within a text, ignoring case; returns the position number if found, or an error if not.
  • ISNUMBER – converts the result to TRUE (1) if found, FALSE (0) if error.
  • — – coerces TRUE/FALSE to 1/0.
  • SUMPRODUCT – adds up the 1s and 0s; if the sum is greater than 0, at least one match exists.

For case-sensitive matching, replace SEARCH with FIND.

Worked Example

Using the default values from the calculator: text to test is “apple pie” and the list is {“apple”,”banana”,”cherry”}.

  1. Check “apple”: SEARCH("apple","apple pie") returns 1 → ISNUMBER(1) → TRUE → 1
  2. Check “banana”: SEARCH("banana","apple pie") returns #VALUE! → ISNUMBER(#VALUE!) → FALSE → 0
  3. Check “cherry”: SEARCH("cherry","apple pie") returns #VALUE! → FALSE → 0
  4. Sum = 1 + 0 + 0 = 1, which is > 0, so the result is “Yes”.

If the text were “banana bread”, the result would still be “Yes”. If it were “pear tart”, all three checks would return 0, so the result would be “No”.

When to Use This Formula

This pattern is used whenever you need to classify, filter, or flag rows based on multiple possible keywords. Common situations include:

  • Checking if a product name contains any of several brand names.
  • Identifying emails from a list of domains (e.g., gmail.com, yahoo.com, outlook.com).
  • Flagging comments that contain any of a set of banned words.
  • Grouping text entries into categories (e.g., if contains “red” or “blue” → color).

The formula works for exact substrings, not exact whole-cell matches. For whole-cell equality, use OR(cell=value1, cell=value2, ...) instead.

How to Build the List of Things

You have three main ways to supply the list of things to look for:

  1. Array constant – type the values directly in the formula with curly braces, e.g., {"apple","banana","cherry"}. Good for a small, fixed list.
  2. Range reference – point to a range of cells, e.g., $B$2:$B$10. This is easier to maintain and expand later.
  3. Named range – define a name (e.g., Keywords) for the list range and use that name in the formula. This makes the formula more readable.

If you use a range, make sure it contains only the words you want to match, with no blank cells (blanks are treated as empty strings, which would match any text).

Common Mistakes and Troubleshooting

  • Case sensitivity – SEARCH ignores case, so “Apple” matches “apple”. If you need case-sensitive, use FIND instead.
  • Partial matches – This formula finds substrings, so looking for “cat” will match “category”. If you need whole-word matches, you must add word boundaries, which is tricky in Excel; a workaround is to check for spaces or punctuation around the word.
  • Empty list – If the list range is empty, SUMPRODUCT returns 0, so the result is “No”. That is usually fine, but be aware.
  • Array entry – In older Excel versions, you may need to press Ctrl+Shift+Enter to enter the formula as an array formula. In Excel 365 and Google Sheets, it works normally.
  • Wildcards – SEARCH supports wildcards like * and ?. If you want to match a literal asterisk, you need to escape it with a tilde (~*).

Alternatives and Variations

Depending on what you need, other formulas may be more suitable:

  • Return the matched value – Instead of just TRUE/FALSE, use =INDEX(list, MATCH(TRUE, ISNUMBER(SEARCH(list, t)), 0)) to get the first matching keyword.
  • Count how many match – Use =SUMPRODUCT(--ISNUMBER(SEARCH(list, t))) to get the number of matches.
  • Check if all match – Use =IF(SUMPRODUCT(--ISNUMBER(SEARCH(list, t)))=COUNTA(list), "All", "Not all").
  • Exact match to any value – Use =IF(COUNTIF(list, t)>0, "Yes", "No").
  • Google Sheets – The same formulas work, but you can also use REGEXMATCH for more powerful pattern matching, e.g., =IF(REGEXMATCH(t, "apple|banana|cherry"), "Yes", "No").

Frequently Asked Questions

Does SEARCH match partial words?

Yes, SEARCH finds substrings. For example, looking for “man” will match “manager”, “human”, and “permanent”. If you need whole words only, you can add spaces around the search term, but that fails at the start or end of the text. A more robust approach is to use a regular expression in Google Sheets or a VBA user-defined function in Excel.

Can I use this with numbers?

Yes, but SEARCH converts numbers to text, so searching for 123 will match the text “123” anywhere in the cell. For exact numeric matches, use COUNTIF or OR with equality.

Why does my formula return #VALUE!?

This usually happens if the list range contains errors, or if you are using an older Excel version and forgot to press Ctrl+Shift+Enter. It can also occur if the list includes a cell with an error value. Check the list range for errors and ensure you are entering the formula correctly.

How do I make it case-sensitive?

Replace SEARCH with FIND. FIND is case-sensitive, so “Apple” will not match “apple”. The rest of the formula stays the same.

Can I check for whole-cell matches instead of substrings?

Yes. For exact matches, use =IF(COUNTIF(list, t)>0, "Yes", "No") or =IF(OR(t=value1, t=value2, ...), "Yes", "No"). These compare the entire cell content, not just a part.

What if I have a long list of things to check?

Use a range reference instead of an array constant. Put the list in a column (e.g., B2:B100) and reference that range in the formula. This keeps the formula short and makes it easy to add or remove items without editing the formula.

Related

Formula used
in t | Text to test | - | apple pie | 0
in list | Things to look for | apple, banana, cherry | 0
out result | Contains any? | - | IF(SUMPRODUCT(--ISNUMBER(SEARCH(list,t)))>0,"Yes","No") | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.