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

## Bookmarks