Highlight multiple cells in list if they meet any one stipulation

Hi All,

I have a very large list of contacts with company names in an excel sheet. Sometimes there are multiple contacts from a given company, but the company name will be written slightly differently (i.e. one guy writes "Pete's Coffee" and another employee writes "Pete's Coffee Ltd"). Of the total 100 companies listed in my sheet, there are about 20 companies (with minor permutations in the written name) which I would like to visually filter out by having Excel highlight the cell with the company name in it. That way I know to contact the person in that row.

I've been trying to do it with an OR statement in a conditional formatting rule, but with no luck. A simple 3 company version of the rule I was trying to make looks like this:

=(OR($D?="*Company 1*",$D?="*Company 2*",$D?="*Company 3*"))

The *s are there to account for any permutations in the company name recorded by people in this list, and (I think) the ?s are there so that the rule gets applied to every D cell (i.e. my company list). I think this is where my problem lies, but I'm not sure how to fix it. Any better ideas?

Thanks in advance.

Thanks TM, that did get me part of the way there. I took your suggestion, but it only worked after I took out the *s in the quoted phrase. I tried it with just the * at the end of the quoted phrase (i.e. "company 1*") but it still wasn't registering any of those cells. This means I can only get excel to read the cells exactly, not allowing for permutations and additions to the end of the cell contents.

Am I doing something wrong with the *s in the quoted parts?

A small example, see attachment.

I'm not sure I understand why the ISNUMBER function makes this work...

It's the MATCH function that makes it work, the ISNUMBER just checks whether MATCH returns a valid result (= a number)