Formula using array to find embedded value
 

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

Formula using array to find embedded value
 

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

Formula using array to find embedded value
 

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" wrote:

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

Formula using array to find embedded value
 

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

"Ted Horsch" wrote:

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" wrote:

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

Formula using array to find embedded value
 

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

"Toppers" wrote:

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

"Ted Horsch" wrote:

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" wrote:

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

Formula using array to find embedded value
 

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


=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),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!

"Ted Horsch" wrote:

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" wrote:

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

Formula using array to find embedded value
 

Better ....

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

Sorry about my earlier errors but hope this helps.

"Toppers" wrote:

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


=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),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!

"Ted Horsch" wrote:

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" wrote:

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

Formula using array to find embedded value
 

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" wrote:

Better ....

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

Sorry about my earlier errors but hope this helps.

"Toppers" wrote:

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


=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),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!

"Ted Horsch" wrote:

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" wrote:

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

Formula using array to find embedded value
 

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<atjohntopley.fsnet.co.uk

in D":

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

"Ted Horsch" wrote:

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" wrote:

Better ....

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

Sorry about my earlier errors but hope this helps.

"Toppers" wrote:

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


=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),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!

"Ted Horsch" wrote:

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" wrote:

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

Formula using array to find embedded value
 

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

"Ted Horsch" wrote:

Toppers,

My mistake, and a silly one. I corrected it and now it works as you say.
You've been very generous with your time -- I am much obliged. Now I'll see
if I can incorporate this into my real-world spreadsheet. Thanks a million!

"Toppers" wrote:

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<atjohntopley.fsnet.co.uk

in D":

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

"Ted Horsch" wrote:

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" wrote:

Better ....

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

Sorry about my earlier errors but hope this helps.

"Toppers" wrote:

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


=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),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!

"Ted Horsch" wrote:

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" wrote:

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