Highlight text in column 1 based on a range of words* in column 2

[gdekker]

Data:
Column G with course names, Column S with a subset of those courses. I would like to highlight every course in G that matches the partial words in S.

I kind of have it solved already by using 2 rules (since the outlier has a different, simpler naming scheme), but this should be possible (and imho is more elegant) with 1.

I'm currently using 2 conditional formatting rules:
=COUNTIF($S$2:$S$200,G2)>0 <- highlights every course in G that matches any course in S exactly
and
=SEARCH("CourseX1-", G2)>0 <- highlights any course in G that starts with 'CourseX1-'

Unfortunately, the search does not work on a range (so "=SEARCH($S$2:$S$200, G2)>0" doesn't work).

The simplest solution would be a rule that would highlight every word in G that starts with any of the words in S. I would think this wasn't that hard, but I can't figure it out. Can anyone help out?

 

[Sergius]

The simplest solution is to use a conditional formatting rule where the text contains what you are looking for.

 

[Fluff]

Hi & welcome to MrExcel.
Do you have any blank cells in S2:S200?

 

[gdekker]

The simplest solution is to use a conditional formatting rule where the text contains what you are looking for.

Thanks for the suggestion, but that is not a simple solution. Dozens of these courses have different naming schemes, I'd have to make dozens of rules. (Note: I want all courses in G highlighted the same color. In your example 1 rule would suffice: "cell value contains 'CourseX'"; one of the 2 rules I have already does exactly that "=SEARCH("CourseX", G2)>0")

Do you have any blank cells in S2:S200?

Not necessarily. (That is: I will add and remove items every now and then, but I can adjust the '200' to the last item's cell number.)

There has to be a simple solution that checks if 'word in colum G (equals or) starts with word in column S'?

 

[Fluff]

Ok, how about

=OR(COUNTIF($S$2:$S$200,G2),SUM(COUNTIFS(G2,$S$2:$S$200&"*")))

But this will not work if you have blanks in col S

 

[gdekker]

Ok, how about

=OR(COUNTIF($S$2:$S$200,G2),SUM(COUNTIFS(G2,$S$2:$S$200&"*")))

But this will not work if you have blanks in col S

Yup, that did the trick, thanks!

 

[Fluff]

You're welcome & thanks for the feedback.