Formula using array to find embedded value

[Ted Horsch]

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

 

[Toppers]

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

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

HTH

 

[Ted Horsch]

Thanks very much for the quick reply.

I tried this solution and it is close but not exactly what I need. When I
replace the "Y" with Corename I get the following results:

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

Actually, the spreadsheet is set up with multiple rows:

C2 = ABCDCCCXYZ returns AAA
D2 = ABCDEFGHIJK returns N
E2 = ABCDEFGHDDD returns CCC
F2 = AABBCCDDXYZ returns N

The range is in Col A rows 1 - 4.

How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD?

 

[Toppers]

How is "corename" defined? Is it a named range?

Thanks very much for the quick reply.

I tried this solution and it is close but not exactly what I need. When I
replace the "Y" with Corename I get the following results:

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

Actually, the spreadsheet is set up with multiple rows:

C2 = ABCDCCCXYZ returns AAA
D2 = ABCDEFGHIJK returns N
E2 = ABCDEFGHDDD returns CCC
F2 = AABBCCDDXYZ returns N

The range is in Col A rows 1 - 4.

How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD?

 

[Toppers]

Ignore my last reply as the answer is in your first posting!

 

[Toppers]

Set up Corindex" in A5 to A8 with numbers 1,2 3, 4


=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Coreindex)),CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Coreindex)),$A$1,$A$2,$A$3,$A$4),"N")

I am sure there is a more elegant solution but this is my best shot!

 

[Toppers]

Better ....

=CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Coreindex))+1,"N",$A$1,$A$2,$A$3,$A$4)

Sorry about my earlier errors but hope this helps.

 

[Ted Horsch]

Topper,

I tried this last formula and it returns a #VALUE error. One thing: I had
a typo below describing my spreadsheet. The rows are actuall as follows:

C2 = ABCDCCCXYZ returns AAA
C3 = ABCDEFGHIJK returns N
C4 = ABCDEFGHDDD returns CCC
C5 = AABBCCDDXYZ returns N

I have A1 - A4 as follows
AAA
BBB
CCC
DDD

And I added A5 - A8 as follows:
1
2
3
4

I put your formula in column D. Looks like this:

Col A Col B Col C Col D
AAA
BBB ABCDCCCXYZ #VALUE!
CCC ABCDEFGHIJK
DDD ABCDEFGHDDD
1 AABBCCDDXYZ
2
3
4

Any other suggestions?

 

[Toppers]

Ted,
I tried it with your data and it worked. Double check the
formula below and if you still get problems, post w/sheet to me at
toppers<at>johntopley.fsnet.co.uk

in D":

=CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Coreindex))+1,"N",$A$1,$A$2,$A$3,$A$4)

 

[Toppers]

Ted,
Thanks for the feedback. I hope it works in the "real" world.