How to If Cell Contains This Or That in Excel

Why This Task Matters in Excel

Imagine you receive a daily CSV export from your e-commerce platform containing thousands of customer comments. Some comments mention “refund,” others mention “exchange,” and some mention both. You want to route comments containing either word to your customer-service queue so that they are handled promptly. In another department, a quality-control analyst scans production notes for the words “defect” or “scrap” to flag batches that require additional testing. In financial reporting, an accountant reviews ledger descriptions that contain “write-off” or “bad debt” to drive specific journal entries.

All of these scenarios boil down to a single, deceptively simple requirement: “If the cell contains this or that, do something.” The action might be returning TRUE or FALSE, labeling the row, applying conditional formatting, filtering a dataset, or driving further calculations.

Excel is tailor-made for this because it combines lightning-fast text-search capabilities with logical functions you can nest or combine in countless ways. Whether you are using a single worksheet, multiple sheets, or an external data connection, Excel lets you automate text detection so humans only review the records that matter. Failure to master this skill means more manual filtering, missed exceptions, and slower decision-making. Conversely, becoming fluent in these techniques directly improves data accuracy, turnaround time, and downstream analytics. Because the logic is reusable, learning it once pays dividends across dashboards, macros, Power Query transformations, and even Power BI models that reference Excel files. In short, knowing how to test “if cell contains this or that” is a foundational skill that underpins many modern spreadsheet workflows.

Best Excel Approach

The most robust method combines the SEARCH function with ISNUMBER inside an OR test, wrapped by IF (or LET for greater efficiency in Microsoft 365). This quartet gives you case-insensitive matching, immunity to hard-to-spot extra spaces, and the flexibility to evaluate many keywords at once.

SEARCH returns the numeric position of a substring. When the substring is not found, Excel throws a #VALUE! error. ISNUMBER converts “numeric position found” into TRUE and “error” into FALSE. OR aggregates multiple TRUE/FALSE results and returns TRUE if any keyword is found. Finally, IF converts the logical test into whatever output you need—text labels, numbers, or further formulas.

Use this approach when you:

• Need case-insensitive matching (most business scenarios).
• Must test more than one keyword.
• Want to avoid wildcard quirks of COUNTIF/COUNTIFS.
• Are comfortable nesting functions and want maximum control over the result.

Syntax template:

=IF(
     OR(
        ISNUMBER(SEARCH("keyword1", A2)),
        ISNUMBER(SEARCH("keyword2", A2))
     ),
     "Match",
     "No Match"
)

Alternative (Microsoft 365 optimized) using LET to calculate SEARCH once per keyword and avoid repetition:

=LET(
    text, A2,
    k1, ISNUMBER(SEARCH("keyword1", text)),
    k2, ISNUMBER(SEARCH("keyword2", text)),
    IF(OR(k1, k2), "Match", "No Match")
)

COUNTIF with wildcards remains a viable fallback for quick single-cell checks or older Excel versions:

=IF(COUNTIF(A2, "*keyword1*")+COUNTIF(A2, "*keyword2*"), "Match", "No Match")

Parameters and Inputs

• Cell to Scan – Typically a single cell like A2, but can be a concatenated text string, the result of a formula, or a structured-table reference such as [@Comment]. Must contain text; numbers will be coerced automatically.

• Keywords – Literal text strings enclosed in double quotes, dynamic cell references (e.g., B$1), or array constants like [\"refund\",\"exchange\"]. SEARCH is case-insensitive; use FIND if case sensitivity is required.

• Output – Anything IF can return: static text, numbers, boolean values, or additional formulas. Plan outputs carefully if downstream functions rely on numeric types.

• Optional: Number of Keywords – OR can take up to 255 arguments, but readability suffers beyond a handful. Consider XLOOKUP or FILTER with an array if you need dozens of keywords.

• Data Preparation – Trim leading/trailing spaces and ensure consistent encoding. Non-breaking spaces copied from web pages can cause false negatives; CLEAN and TRIM can help.

• Validation – Confirm keywords have no extra spaces, hidden characters, or inconsistent capitalization (if using FIND). When referencing a list of keywords, wrap the range in TEXTJOIN to create a dynamic pattern or use BYROW/LAMBDA in Microsoft 365.

Edge cases: Empty cell returns \"No Match\" because SEARCH on an empty string returns 1 but is not meaningful; wrap a LEN test around the main formula if you want blanks to return blank.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose column A contains customer survey comments. You want to tag any comment that contains either “late” or “delay.”

1. Enter sample data:
   A2: “Package arrived two days late.”
   A3: “Great service.”
   A4: “Delivery delay was frustrating.”

2. In B2 type the formula:

=IF(
     OR(
        ISNUMBER(SEARCH("late", A2)),
        ISNUMBER(SEARCH("delay", A2))
     ),
     "Needs Attention",
     "OK"
)

3. Copy B2 down to B4.

• B2 returns “Needs Attention” because SEARCH finds “late.”
• B3 returns “OK.”
• B4 returns “Needs Attention” because SEARCH finds “delay.”

Why it works: SEARCH looks for the substring regardless of position, punctuation, or capitalization. ISNUMBER converts the position returned (e.g., 25) into TRUE. OR decides if any keyword matched. IF converts that into a friendly label.

Common variations: Replace \"Needs Attention\" with 1 and \"OK\" with 0 for pivot-table aggregation or SUM calculations. Swap SEARCH for FIND if your keywords are case-sensitive (e.g., distinguishing “Late” the surname from “late” meaning delayed).

Troubleshooting tips: If every result shows “Needs Attention,” one of your keywords might be blank, yielding ISNUMBER(SEARCH(\"\",A2)) = TRUE. Wrap an explicit LEN check around each keyword or store keywords in cells that cannot be blank.

Example 2: Real-World Application

Scenario: A logistics manager reviews a dispatch log in a structured table named DispatchLog. Column [StatusNote] contains free-form text from drivers. The manager must flag records that mention either “flat tire” or “engine” for maintenance reporting and then load the clean subset into Power Query.

1. Data setup: The table has columns [Date], [TruckID], [StatusNote]. Example rows:

• “Minor engine noise observed.”
• “Route completed on time.”
• “Stopped due to flat tire near exit 14.”

2. Insert a new column named [MaintenanceFlag]. In the first data row of that column enter:

=IF(
     OR(
        ISNUMBER(SEARCH("flat tire", [@StatusNote])),
        ISNUMBER(SEARCH("engine", [@StatusNote]))
     ),
     TRUE,
     FALSE
)

Because it is a structured table, Excel automatically copies the formula to every row.

3. Apply a filter on [MaintenanceFlag] for TRUE. You now see only the trucks requiring maintenance.

4. Load the filtered table into Power Query (Data ➜ From Table/Range) and configure your ETL pipeline knowing that maintenance records are pre-tagged.

Key business value: The dispatch supervisor no longer reads every note manually. Downstream analyses such as cost of repairs, average downtime, and preventive maintenance scheduling can be automated.

Integration: You can use the same flag in a conditional formatting rule to color the entire row red, or feed it into a PivotTable to count incidents by month.

Performance: On a 10 000-row table this formula recalculates almost instantly because it only scans two keywords per cell. For more extensive keyword lists, consider moving logic into Power Query’s Text.Contains with an OR grouping or using a single SEARCH for an array of keywords (Microsoft 365).

Example 3: Advanced Technique

Problem: A compliance officer must scan transaction narratives for any of 15 high-risk phrases stored in range [J2:J16]. The list changes monthly. The workbook must remain compatible with Excel 2016.

1. Combine keywords into a single pattern. In K2 use:

="*"&TEXTJOIN("*",TRUE, $J$2:$J$16)&"*"

This returns a wildcard string like fraudbribeembezzle...

2. In the transactions table (column [Narrative]), enter in row 2:

=IF(
     SUMPRODUCT(--ISNUMBER(SEARCH($J$2:$J$16, [@Narrative])))>0,
     "High Risk",
     "Normal"
)

Explanation: SEARCH returns an array of positions for each keyword. ISNUMBER converts to TRUE/FALSE. The double unary (--) coerces booleans to 1/0. SUMPRODUCT adds them; any value greater than zero indicates at least one match.

3. Copy down. Update the keyword list at any time—no formula edits required.

Edge cases handled: Blank rows in the keyword list are ignored by the TRUE argument in TEXTJOIN. SEARCH naturally ignores case, ensuring phrases like “Fraud” and “fraud” both match.

Optimization: In Microsoft 365 you could simplify with:

=IF(
    BYROW(
       SEARCH(TRANSPOSE($J$2:$J$16), [@Narrative]),
       LAMBDA(r, OR(ISNUMBER(r)))
    ),
    "High Risk",
    "Normal"
)

Professional tips: Store the keyword list in a hidden sheet so only authorized staff can edit it, and protect the definition workbook before distributing to branch offices.

Tips and Best Practices

1. Normalize Data Early: Use TRIM, CLEAN, and LOWER on incoming text to remove irregular spacing and make case behavior predictable.
2. Use Named Ranges: Assign a name like KeywordList to [J2:J16] so formulas read `=SEARCH(`KeywordList, A2), improving readability.
3. Minimize Volatile Functions: Avoid repeating SEARCH for the same keywords in multiple columns. Wrap helpers in LET or calculate once in a helper column.
4. Conditional Formatting Synergy: After your IF formula is stable, replicate the logical test directly in a conditional-formatting rule to highlight matches without additional columns.
5. Document Your Logic: Add a comment or note explaining the purpose of each keyword and the rationale for including it; future maintainers will thank you.
6. Test on a Sample First: Run your formula on a subset before deploying across 100 000 rows to catch misspellings, blank keywords, and unexpected matches.

Common Mistakes to Avoid

1. Forgetting Wildcards in COUNTIF: COUNTIF(\"word\") requires asterisks. Omitting them makes COUNTIF look for an exact cell value, returning zero unexpectedly.
2. Using FIND Instead of SEARCH Unintentionally: FIND is case-sensitive; if you switch unknowingly, matches like “Refund” vs “refund” disappear. Always confirm which function you need.
3. Allowing Blank Keywords: A blank string evaluates as found at position 1, causing every record to return TRUE. Validate your keyword list with a COUNTA check.
4. Over-Nesting: Three-level IF-OR-AND nests are hard to audit. Break them into helper columns or use LET to clarify each step.
5. Ignoring Performance: Applying 50 SEARCH calls per cell over 200 000 rows can slow recalculation. Consolidate keywords into arrays or move logic into Power Query for massive datasets.

Alternative Methods

Switch methods when:

• Upgrading to Microsoft 365—replace nested formulas with dynamic arrays for cleaner sheets.
• Your keyword list exceeds 50 entries—consider Power Query or a VBA custom function.
• Performance lags—move logic to the data source, SQL, or Power Query.

FAQ

When should I use this approach?

Use SEARCH + ISNUMBER when you need case-insensitive detection of one or more keywords and want the result to update instantly whenever the cell or keyword list changes. It excels in interactive models, dashboards, and automated tagging workflows.

Can this work across multiple sheets?

Yes. Reference the cell or keyword list by prefixing the sheet name: SEARCH("refund", 'January Data'!A2). Arrays like "*late*" work identically. Just ensure both sheets are open; external files may trigger #REF! errors when closed.

What are the limitations?

SEARCH cannot use regular expressions and treats the wildcard question mark literally. Very large keyword lists (hundreds) can slow calculation. FIND and SEARCH both count characters, so languages with surrogate pairs might behave inconsistently. Consider Power Query or VBA for advanced pattern matching.

How do I handle errors?

Wrap your entire expression in IFERROR to return a custom message. Example: =IFERROR(YourFormula, "Check Input"). Also validate that text cells are not numeric or error values before running SEARCH.

Does this work in older Excel versions?

Yes. SEARCH, ISNUMBER, OR, and IF exist as far back as Excel 97. LET, FILTER, and BYROW require Microsoft 365 or Excel 2021. If you need compatibility with Excel 2003, stick to traditional formulas or use COUNTIF with wildcards.

What about performance with large datasets?

Reduce function calls by combining keywords into arrays, offload heavy lifting to Power Query, and turn off automatic calculation while pasting large datasets. Where possible, move text search to the database layer or use VBA to scan once and store results.

Conclusion

Mastering the “if cell contains this or that” pattern turns Excel from a simple grid into a rule-based engine that can triage customer feedback, flag compliance issues, or surface critical production notes without human oversight. The core logic—SEARCH, ISNUMBER, OR, and IF—forms a reusable template that integrates seamlessly with conditional formatting, PivotTables, and Power Query. By practicing the examples, adopting best practices, and avoiding common pitfalls, you are well on your way to automating repetitive text searches and delivering faster, more reliable insights. Next, experiment with dynamic arrays or Power Query to expand your toolkit and keep optimizing your workflow.