If Cell Contains This or That: Excel Formula Guide

Updated 14 November 2026 · Standard formula, shown below

This guide explains how to check if a cell contains any of several specified values in Excel or Google Sheets, and returns a result based on that check. You will learn the core formulas using IF, OR, and ISNUMBER/SEARCH, plus how to handle partial matches, case sensitivity, and multiple conditions. The answer to the direct question: use =IF(OR(ISNUMBER(SEARCH("this",A1)),ISNUMBER(SEARCH("that",A1))),"Yes","No") for a case-insensitive partial match, or =IF(OR(A1="this",A1="that"),"Yes","No") for an exact match.

Understanding the Core Functions

Excel and Google Sheets offer several functions that combine to create a “contains this or that” test. The two most common approaches are:

Exact match: The cell must equal one of the specified values entirely. Use the OR function with simple equality comparisons.
Partial match (substring): The cell contains the specified text anywhere within it. Use ISNUMBER combined with SEARCH (case-insensitive) or FIND (case-sensitive).

The OR function returns TRUE if any of its arguments are TRUE. The ISNUMBER function converts the result of SEARCH (which returns a position number or an error) into a TRUE/FALSE value. By nesting these, you can test multiple substrings at once.

Basic Formula for Exact Matches

If you need to check whether a cell equals exactly “this” or “that”, the formula is straightforward:

=IF(OR(A1="this", A1="that"), "Yes", "No")

This works for numbers too: =IF(OR(A1=10, A1=20), "Yes", "No"). It is case-insensitive for text? Actually, the = operator is case-insensitive in Excel, so “This” and “THIS” are treated as equal. For case-sensitive exact matches, you would need EXACT, but that is less common.

Formula for Partial Matches (Contains)

To check if a cell contains the substring “this” or “that” anywhere (e.g., “this product” or “something that works”), use:

=IF(OR(ISNUMBER(SEARCH("this",A1)), ISNUMBER(SEARCH("that",A1))), "Yes", "No")

SEARCH returns the starting position of the substring if found, and a #VALUE! error if not. ISNUMBER converts that to TRUE for a position and FALSE for an error. The OR then combines the two checks. This formula is case-insensitive, so “This” and “THIS” both match.

Handling Case Sensitivity

If you need a case-sensitive partial match, replace SEARCH with FIND:

=IF(OR(ISNUMBER(FIND("this",A1)), ISNUMBER(FIND("that",A1))), "Yes", "No")

Now “This” will not match “this”, but “this” will. Note that FIND does not support wildcards, while SEARCH does (e.g., SEARCH("th?t",A1) matches “that” and “thit”).

Using Wildcards for Pattern Matching

Both SEARCH and the COUNTIF function support wildcards: * (any sequence), ? (single character), and ~ (escape). For example, to check if a cell contains any word starting with “th” and ending with “t”, you could use:

=IF(COUNTIF(A1,"th*t")>0, "Yes", "No")

But COUNTIF is case-insensitive and works for whole cell patterns. For multiple patterns, you can use SUMPRODUCT with ISNUMBER(SEARCH()) for an array of substrings.

Checking Against a List of Values

When you have many values to check, listing them inside the formula becomes unwieldy. Instead, put the values in a range (e.g., D1:D5) and use an array formula:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$5,A1)))>0, "Yes", "No")

This works in both Excel and Google Sheets (in older Excel, you may need to enter with Ctrl+Shift+Enter). The -- converts TRUE/FALSE to 1/0, and SUMPRODUCT adds them; if the sum is greater than 0, at least one match exists. For exact matches against a list, use COUNTIF:

=IF(COUNTIF($D$1:$D$5,A1)>0, "Yes", "No")

Common Mistakes and Troubleshooting

Forgetting the ISNUMBER wrapper: SEARCH returns a number or an error, not TRUE/FALSE. If you use IF(SEARCH("this",A1)>0,...), it will fail when the substring is not found because the error propagates.
Using OR with SEARCH directly: OR(SEARCH("this",A1), SEARCH("that",A1)) will return TRUE if either search succeeds, but if a search fails, it returns an error, causing the whole formula to error. Always wrap each search in ISNUMBER.
Case sensitivity confusion: SEARCH is case-insensitive; FIND is case-sensitive. Choose based on your need.
Trailing spaces: If your cell has extra spaces, exact matches may fail. Use TRIM to clean data first.
Wildcard issues with FIND: FIND does not support wildcards, so if you need them, use SEARCH or COUNTIF.

Frequently Asked Questions

How do I check if a cell contains any of several words, not just two?

Use a list-based formula: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$5,A1)))>0, "Yes", "No") where D1:D5 contains your words. For exact matches, use =IF(COUNTIF($D$1:$D$5,A1)>0, "Yes", "No").

Can I return a different value for each match?

Yes, but it requires nested IFs or a lookup. For example: =IF(ISNUMBER(SEARCH("this",A1)), "This found", IF(ISNUMBER(SEARCH("that",A1)), "That found", "None")). Note that this checks in order, so if both are present, it returns the first match.

Why does my formula return #VALUE! instead of “No”?

This usually happens when you use SEARCH or FIND directly inside OR without wrapping each in ISNUMBER. The error from a non-match propagates. Always wrap each search with ISNUMBER.

Is there a way to make the formula case-sensitive for exact matches?

Yes, use the EXACT function: =IF(OR(EXACT(A1,"this"), EXACT(A1,"that")), "Yes", "No"). This treats “This” and “this” as different.

Does this work in Google Sheets?

Yes, all formulas shown work identically in Google Sheets, except for array entry (in Google Sheets, you don’t need Ctrl+Shift+Enter for SUMPRODUCT). The SEARCH and FIND functions behave the same.

How do I check if a cell contains “this” but NOT “that”?

Use =IF(AND(ISNUMBER(SEARCH("this",A1)), NOT(ISNUMBER(SEARCH("that",A1)))), "Yes", "No"). This combines the contains check with a negative check.

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