Excel 2007 : How do I find cell matches one of multiple criteria?

In Column A, lets say I have a range of names.
In Column B, I want to use an IF statement to identify IF the corresponding cell in A has the name Bob or Jill.

This is what I thought it should be:

IF(A1={"Bob", "Jill"}, 0,1)

So when I use the fill handle for the entire column, everywhere with a "Bob" or a "Jill" should be a 0. Instead, it is only finding the "Bob" and not the "Jill". Am I missing some syntax issue here? Thanks if anyone can help.

You mean like this:

=IF(OR(A1={"Bob", "Jill"}),0,1)

Yes, thanks. I knew I was missing something simple.

And this was a "dummy" problem associated with something else I am doing. Hopefully now I can do it. If not, I will be back. Thumbs up for prompt reply!

OK I am back.

So the big picture is I am trying to conditional format a range based on multiple criteria. In column A I have list of names. Column B is what I am trying to format. I am trying to create a formula such that whenever there is "Bob" or "Jill" in Column A, the corresponding cell in column B is highlighted. I tried using:

=IF(OR(A1:A53={"Bob", "Jill"}, 0, 1)=0

Any advice?

So for 10 criteria, I would have to write each in that format?

For 10, 100, 1000 criterias would be better to create list (for example in C column) and then you can use MATCH or COUNTIF or some other approach to check criteria.


For example:

=COUNTIF(C:C, A1)>0

Thanks. I am still learning the syntax such as references and how $A1 is different from A$1, and am learning if you mess that up, then you get a headache .

It's very important part of the excel.
Try look here for example: http://www.youtube.com/watch?v=NmVMjQzseLA

And deal with it once for all