Conditional formatting: Need to highlight a cell if it contains partial text from another column

user_14291578 0
Viewed 416

Here's the issue: Column A contains a list of search terms (like "plumber near me" and "plumbing jobs near me"), Column B contains a list of negative terms (like "jobs", "hiring", etc.). I want to highlight just the cells in column A that match any of the terms in column B. In other words "plumbing jobs near me" would highlight red because it contained a negative term. I know how to do it for a single cell, but can't figure out how to do it for all of column B. Here's what I've used:

=COUNTIF(A2,"*"&$B$2&"*")>0

and

=COUNTIF(A2,"*"&$B$2:$B$100&"*")>0

then copied just the formula to the other cells in A. It works for the negative term in B2 - but no matter what I put between the wildcards for column B, including B:B, B2:B100, etc. - it won't grab all the terms in the column.

In other words, I want it to do something like this:

A                            B
plumbing company             jobs
plumber near me              hiring
**plumbing jobs**            how to
emergency plumber
**company hiring plumbers**

Help please!

excel-formula
1 Answers

You have an array formula =COUNTIF(A2,"*"&$B$2:$B$100&"*")>0 - it will return an array of outputs rather than one. You're checking if any one of those outputs is true, so try =SUM(COUNTIF(A2, "*"&$B$2:$B$100&"*"))>0.

NB I have only tried this in Excel for Office 365, in other versions of office you have to hit Ctrl+Shift+Enter to use an array formula and I don't know if it would work the same way.

Your question implies that you are typing the formula in a column:

then copied just the formula for the other cells in A

but also that you want to highlight cells red.

To do this in conditional formatting without adding the formula in a column in your spreadsheet, select the range to format and add a new rule (the conditional formatting dropdown is in the home tab). Select 'use a formula to determine which cells to format', then select your desired formatting and use the formula mentioned above, =SUM(COUNTIF($A2, "*"&$B$2:$B$100&"*"))>0 - editing $A2 to reflect the top row of the range you are formatting (ie if your range to be formatted starts at row 3, change $A2 to $A3 in the formula).

user_122319840
Related