I'm trying to highlight cells that don't contain specific text from a list in Excel 365

Question

I'm trying to highlight cells that DO NOT contain specific terms. I have seen other posts that will highlight the listed terms, but not one that will highlight the cells that do not contain the terms.

In Sheet1, Column A, I have a long list of Job Titles.

Job Title
Marketing Manager
Director of Planning
Cat Wrangler
Mktg Mgr
Asphalt Marketer

In Sheet2, Column B, I have a list of terms that I want to search for:

Terms
Marketing
Mktg
Marketer

I would like to use Conditional Formatting in Sheet1, Column A to highlight the job titles that DO NOT appear in Sheet 2, Column B

So for the above list, the following cells would be highlighted in Sheet1, Column A:

Job Title
Director of Planning
Cat Wrangler

These words would not be highlighted:

Job Title
Marketing Manager
Mktg Mgr
Asphalt Marketer

Answer 1

Try-

=SUM(IFERROR(SEARCH($E$2:$E$4,$A2),""))=0

In case of Sheet2 use-

=SUM(IFERROR(SEARCH(Sheet2!$B$2:$B$4,$A2),""))=0

Answer 2

Using new BETA-functionality for exact matching:

Formula applied to A1:A5:

=SUM(--(TEXTSPLIT($A1," ")=C$1:C$3))=0

Or, if not available, on can follow the other answer perfectly fine with concatenated spaces in lookup-value and lookup-range.

Answer 3

As mentioned in comments above, if you are using O365, then please try,

• Formula used in Conditional Formatting

=NOT(ISNUMBER(--CONCAT(IFERROR(SEARCH(" "&$C$2:$C$4&" "," "&A2&" "),""))))

---

And If your search value is in Sheet2 then,

 =NOT(ISNUMBER(--CONCAT(IFERROR(SEARCH(" "&'Sheet2'!$B$2:$B$4&" "," "&'Sheet1'!A2&" "),""))))

---

Or, If you don't have access to O365 then try using with LOOKUP() Function with SEARCH() & ISNA() Function, LOOKUP() certainly helpful in such scenarios.

• Formula used in Conditional Formatting

=ISNA(LOOKUP(9^9,SEARCH($C$2:$C$4,A2)))

---

With Sheet2 use

=ISNA(LOOKUP(9^9,SEARCH('Sheet2'!$B$2:$B$4,'Sheet1'!A2)))

---