Function to determine if any cell in a range is contained in a given cell

[choxio]

Is there an Excel function that can determine if any value in a range
is contained in a given cell.

Ex

Range - CC, DD, EE, FF, QQ
Cell - Yabbadee

Desired function would indicate that EE was found in cell (last 2 chars
of Yabbadee).

VLOOKUP would not indicate that EE was found in the cell (exact match
required). If the cell value however was CC, DD, EE, FF, or QQ it
would find a match.

TIA

 

[Jason Morin]

With the your range in A1:A5 and the word in C1, put this
in B1 and fill down:

=IF(COUNTIF($C$1,"*"&A1&"*"),"found","")

HTH
Jason
Atlanta, GA

 

[Domenic]

Also, if you wish to return the result in one cell...

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(B1:B5,A1)))),"Found","Not Found")

OR

=IF(SUMPRODUCT(COUNTIF(A1,"*"&B1:B5&"*")),"Found","Not Found")

Hope this helps!

 

[Choxio]

Domenic and Jason,

I thank you both so much. It was really getting under my skin that I
couldn't find an answer for this. And I have one further question to
you both. Do either of you have suggestions on a good example-driven
web site or book covering more advanced usage of functions. I was at
the point where I was going to write a VB macro to iterate through the
range. Actually if you have suggestions for a great VB macro book site
I would find that most useful as well.

Again thanks so much :->