I’m 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 I’ll 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