To check whether an Excel cell contains specific text, use IF with ISNUMBER and SEARCH. With a description in A5 and the text you want to find in F2, enter:
=IF(ISNUMBER(SEARCH($F$2,A5)),"Match","No match")
If F2 contains apple, this returns Match for both Red apple and APPLE pie. It also matches Pineapple: the formula looks for those letters anywhere in the cell, including inside another word.
For a search box that you might clear, use this version instead:
=IF($F$2="","",IF(ISNUMBER(SEARCH($F$2,A5)),"Match","No match"))
The extra IF keeps the result visually blank until you enter a search term. We'll use that guard in the examples below.
You can download the example workbook to follow the same descriptions and compare the formulas side by side.
Set up a description list
Suppose you have a short product list and want to flag descriptions containing apple. On a blank worksheet, put the article title in A1, enter Description in A4, and add these values:
| Cell | Enter this description |
|---|---|
| A5 | Red apple |
| A6 | APPLE pie |
| A7 | Pineapple |
| A8 | Pear tart |
| A9 | Leave this cell empty |
| A10 | Apple and pear mix |
Leave A9 genuinely empty; don't type the instruction from the table. Enter apple in F2 and pear in G2, without quotation marks. We'll use G2 when we get to two search terms.
Enter Contains text in B4. Put the guarded formula in B5, then copy it down through B10:
=IF($F$2="","",IF(ISNUMBER(SEARCH($F$2,A5)),"Match","No match"))
The dollar signs keep $F$2 fixed as you copy down. The reference A5 changes to A6, A7, and so on, so each row checks its own description.
| Description | Result with F2 = apple |
|---|---|
| Red apple | Match |
| APPLE pie | Match |
| Pineapple | Match |
| Pear tart | No match |
| Empty cell | No match |
| Apple and pear mix | Match |
Check B7 in particular. Pineapple is supposed to match here. If that isn't the result you want, see the section on whole cells and whole words below.
How IF, ISNUMBER, and SEARCH work together
Three functions can look like a lot for a yes-or-no question. Read this one from the inside out:
SEARCH($F$2,A5)looks forappleinRed apple. The first matching letter is character 5, so it returns5.ISNUMBERchecks that result. Since5is a number, the result isTRUE.IFuses thatTRUEto returnMatch. AFALSEresult returnsNo match.
For Pear tart, SEARCH can't find apple and returns #VALUE!. ISNUMBER treats that error as a nonnumeric result and returns FALSE, so the complete formula shows No match.
Microsoft recommends this same combination for checking whether part of a cell matches specific text.
The outer IF($F$2="","",...) handles a different problem: an empty search term. Without this check, searching for an empty string can return a position and make an ordinary description appear to match. The guard returns "", which looks blank, instead of classifying the row.
You can replace "Match" and "No match" with labels that suit your report. Keep quotation marks around text results. To return numbers, use unquoted values such as 1 and 0.
Make the search case-sensitive with FIND
If apple and APPLE should produce different results, replace SEARCH with FIND. Put Case-sensitive in C4, enter this formula in C5, and copy it down:
=IF($F$2="","",IF(ISNUMBER(FIND($F$2,A5)),"Match","No match"))
With lowercase apple in F2, you'll get:
| Description | SEARCH result | FIND result |
|---|---|---|
| Red apple | Match | Match |
| APPLE pie | Match | No match |
| Pineapple | Match | Match |
| Pear tart | No match | No match |
| Empty cell | No match | No match |
| Apple and pear mix | Match | No match |
FIND still searches inside words. Its difference here is letter case: uppercase A in Apple and pear mix doesn't match the lowercase a in apple. Microsoft documents the case-sensitive IF, FIND, and ISNUMBER pattern.
Use COUNTIF as an alternative
COUNTIF can check a single cell as well as a range. Enter COUNTIF in D4, put this formula in D5, and copy it down:
=IF($F$2="","",IF(COUNTIF(A5,"*"&$F$2&"*")>0,"Match","No match"))
The & signs join an asterisk, the contents of F2, and another asterisk. With apple in F2, the criterion becomes *apple*, meaning the cell can have text before or after apple.
Because the range is just A5, the count is either 1 or 0. The >0 test turns that count into the condition for IF. This version gives the same results as the SEARCH example for our description list, including APPLE pie and Pineapple.
COUNTIF ignores letter case. Its asterisks are wildcards, as explained in Microsoft's COUNTIF documentation.
Searching for an actual asterisk or question mark
Both SEARCH and COUNTIF interpret * as any sequence of characters and ? as one character. If those symbols are part of a product description, an unescaped search can match more than you intended.
In F2, enter ~* to search for a literal asterisk, or ~? for a literal question mark. For example, apple~? searches for the characters apple?. Use ~~ to represent a literal tilde in a wildcard pattern. Microsoft's SEARCH reference explains its wildcard rules.
These input rules apply to the SEARCH and COUNTIF formulas above. FIND doesn't use wildcards, so enter the actual character when using the case-sensitive version.
Check for either of two terms, or require both
Keep apple in F2 and pear in G2. Now we can distinguish descriptions containing either term from descriptions containing both.
Match either term with OR
The OR function passes when at least one test is true. Enter Either term in E4, put this formula in E5, and copy it down:
=IF(OR($F$2="",$G$2=""),"Enter both terms",IF(OR(ISNUMBER(SEARCH($F$2,A5)),ISNUMBER(SEARCH($G$2,A5))),"Match","No match"))
This checks for apple or pear anywhere in the description. Red apple and Pear tart both return Match.
Require both terms with AND
The AND function requires every test to be true. Enter Both terms in F4, put this formula in F5, and copy it down:
=IF(OR($F$2="",$G$2=""),"Enter both terms",IF(AND(ISNUMBER(SEARCH($F$2,A5)),ISNUMBER(SEARCH($G$2,A5))),"Match","No match"))
Compare the results:
| Description | Either term: OR | Both terms: AND |
|---|---|---|
| Red apple | Match | No match |
| APPLE pie | Match | No match |
| Pineapple | Match | No match |
| Pear tart | Match | No match |
| Empty cell | No match | No match |
| Apple and pear mix | Match | Match |
The AND version doesn't require the terms to be next to each other or in a particular order. It requires a successful search for each one. These are still substring searches, so Pineapple satisfies the apple test.
Both formulas deliberately return Enter both terms if either input is empty. Even the OR example requires you to supply both search terms; it doesn't treat an empty input as an optional term. That prevents an empty search from creating misleading matches.
Contains text, equals text, or contains a whole word?
Choose what counts as a match before copying a formula through a long list:
| What you need | Example with apple | Approach |
|---|---|---|
| Text anywhere in a cell | Match Red apple and Pineapple | SEARCH, or FIND if case matters |
| The entire cell equals the search text | Match apple, but not Red apple | Compare the cell with F2 |
| A complete word within a description | Match Red apple, but not Pineapple | Define which spaces and punctuation separate words |
For a whole-cell comparison, use:
=IF($F$2="","",IF(A5=$F$2,"Match","No match"))
With apple in F2, this returns No match for every description in our example. A cell containing only apple or APPLE would match: Excel's ordinary = text comparison ignores case. See comparing two columns in Excel if you're matching complete values across lists.
A whole-word check is a separate requirement. Adding spaces around a search term can exclude Pineapple, but it can also miss apple, or apple. unless you account for punctuation. The SEARCH, FIND, and COUNTIF formulas shown here don't enforce word boundaries.
When the result isn't what you expected
| Symptom | What to check |
|---|---|
| SEARCH alone shows #VALUE! | A missing match produces this error. The ISNUMBER wrapper converts it to FALSE for IF. |
| Every populated description matches after clearing F2 | Use the formula with the empty-input guard. |
| An empty description shows No match | That's the expected result with apple in F2. An empty source cell is different from an empty search term. |
| APPLE matches apple | SEARCH and COUNTIF ignore case. Use FIND when uppercase and lowercase must differ. |
| Pineapple matches apple | This is a substring match. Decide whether you need a whole-cell or whole-word check. |
| A copied formula starts using the wrong search cell | Keep the search input fixed as $F$2; leave A5 relative so the description row changes. |
| A formula won't accept a text label | Use straight double quotes around labels, such as "Match". Cell references such as $F$2 don't take quotes. |
If the letters look right but a match is missing, check the search input for leading or trailing spaces. A cell containing a space isn't empty, so it passes the blank-input guard and searches for that space. Clear F2 and retype the intended term before changing the formula.
Also check the description itself for an existing error. ISNUMBER(SEARCH(...)) returns FALSE for an error result, whether the cause is a missing match or an error in the source cell. A No match label doesn't establish that the source data is valid.