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.

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

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:

These words would not be highlighted:

CodePudding user response：

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

CodePudding user response：

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

• 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)))