Replace the "Y" (including quotes) with 'Corename'

=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N")

HTH

"Ted Horsch" wrote:

Im using the following formula to determine if a set of values (CoreNames)
are present anywhere in cell C2:

=IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N")

For example, if CoreName is a range containing four entries

CoreName
AAA
BBB
CCC
DDD

And my spread sheet contains these values in Col C Ill get the following
results

C2 = ABCDCCCXYZ returns Y
C2 = ABCDEFGHIJK returns N
C2 = ABCDEFGHDDD returns Y
C2 = AABBCCDDXYZ returns N

This works perfectly for me as is. However, I now need to go a step further
and return the specific CoreName value instead of returning Y. For example,
I want the following results from the values in Col C:

C2 = ABCDCCCXYZ returns CCC
C2 = ABCDEFGHIJK returns N
C2 = ABCDEFGHDDD returns DDD
C2 = AABBCCDDXYZ returns N

Can this be done with an enhancement to my formula above? Excel 2003 SP2