# Formula using array to find embedded value

1. ## Formula using array to find embedded value

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

2. ## RE: Formula using array to find embedded value

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

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

HTH

"Ted Horsch" wrote:

> 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
>

3. ## RE: 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&"*")),Corename,"N")
>
> HTH
>
> "Ted Horsch" wrote:
>
> > 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
> >

4. ## RE: 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&"*")),Corename,"N")
> >
> > HTH
> >
> > "Ted Horsch" wrote:
> >
> > > 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
> > >

5. ## RE: Formula using array to find embedded value

"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&"*")),Corename,"N")
> > >
> > > HTH
> > >
> > > "Ted Horsch" wrote:
> > >
> > > > 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
> > > >

6. ## RE: 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&"*"),(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!

"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&"*")),Corename,"N")
> >
> > HTH
> >
> > "Ted Horsch" wrote:
> >
> > > 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
> > >

7. ## RE: Formula using array to find embedded value

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.

"Toppers" wrote:

> 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!
>
> "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&"*")),Corename,"N")
> > >
> > > HTH
> > >
> > > "Ted Horsch" wrote:
> > >
> > > > 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
> > > >

8. ## RE: 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&"*"),(Coreindex))+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&"*"),(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!
> >
> > "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&"*")),Corename,"N")
> > > >
> > > > HTH
> > > >
> > > > "Ted Horsch" wrote:
> > > >
> > > > > 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
> > > > >

9. ## RE: 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<at>johntopley.fsnet.co.uk

in D":

=CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Coreindex))+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&"*"),(Coreindex))+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&"*"),(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!
> > >
> > > "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&"*")),Corename,"N")
> > > > >
> > > > > HTH
> > > > >
> > > > > "Ted Horsch" wrote:
> > > > >
> > > > > > 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
> > > > > >

10. ## RE: 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<at>johntopley.fsnet.co.uk
> >
> > in D":
> >
> > =CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Coreindex))+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&"*"),(Coreindex))+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&"*"),(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!
> > > > >
> > > > > "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&"*")),Corename,"N")
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > "Ted Horsch" wrote:
> > > > > > >
> > > > > > > > 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
> > > > > > > >

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1