"CONTAINS" formula?

[orutulsa]

I'm trying to have a given cell return one of three variables based on whether a range of cells contains any of certain criteria.

Assume cells B2 through B18 each contain at least one of the following "Bob", "Tom" or "Harry".

In cell B20, I want to return a value of "Tuesday" if ANY of the B2-B18 cells contain "Bob".

I want to return a value of "Wednesday" if ANY of the B2-B18 cells contains "Tom" and a value of "Thursday" if ANY of the B2-B18 cells contain "Harry".

Is there a formula which will do this? Thanks

[mikerickson]

Welcome to the forum.

What do you want the value to be if B2 is "Bob", B3 "Tom" and B4 "Harry"

[orutulsa]

You've made me re-think this a little bit. Thank you.

Let's simplify this a bit and say:

In Cell B20, if B2 to B18 contains Bob or Harry or both, return "Tuesday".

Then I'll repeat the process in cell b21, saying, if B2 to B18 contains Tom or Harry or both, return "Wednesday".

Does that make sense? Thanks

[Ron Coderre]

Try something like this:

Please Login or Register  to view this content.

Or...do you mean the cells could contain phrases that include names,
like "Is that Harry, Bob?"

If that's the case, try this:

Please Login or Register  to view this content.

Does that help?

[orutulsa]

We're close with this formula:

B20: =IF(COUNT(MATCH({"Bob","Harry"},$B$2:$B$18,0)),"Tuesday","")

The missing piece is if one of the source cells contains Bob as part of a larger phrase, such as "Bobby" or "Bobby-Douglas" I would still want it to return the assigned value of "Tuesday". And the same would be true of "Harry". I'm looking for that arrangement of letters, even if it's attached to something else. So if one of the source cells contained "HarryAlligator", I still would want to it to see that "Harry" was contained within that cell and return a value of "Tuesday".

Thanks so much for your help!

[orutulsa]

Ooops - It appears that the second formula option you provided does exactly what I just outlined moments ago. I got a little tunnelvision there and missed that second formula. Thanks again.