Conditional Formatting for many different values

Question

I've been trying to create a conditional format formula that looks in a column and finds a series of names and colors them the same color. I want to get this done with one formula instead of a bunch of formulae.
enter image description here

I have tried INDIRECT, VLOOKUP and to work with a statement from How to fill cells based on complicated formula?. I'd like something like this: Excel dynamic conditional formatting based on cell version 2 but am having trouble converting it to text instead of date.

Could you provide an exact example of what the sheet would look like with the conditional formatting? Those 2 questions are very different and makes it unclear what you are looking for. — chancea
does that picture make sense? so like a conditional statement for text contains almost but with multiple names — TLDAN
Not entirely. So are your 2 search strings dan and frank joe sarah? And if so why is mark highlighted? — chancea
I understand it is a proof of concept, you did well showing your desired result but what is your input? What exactly does B dan : color red frank joe sarah : color red cliff mean? Are your inputs dan, frank, joe, sarah? — chancea
I think I see, after looking back at the edits you had line breaks, if you want your question formatted with line breaks and such you can highlight the text and click on the {} button to keep the formatting properly. However I am still wondering why the sarah vs mark — chancea

pnuts pnuts · Accepted Answer · 2014-12-18T20:33:31

If you have two lists of names, say in ColumnA and ColumnI, and wish to highlight those in A that feature in I please select columnA and try applying a Conditional Formatting formula rule of:

  =MATCH(A1,I:I,0)>0

Conditional Formatting for many different values

1 Answers