+ Reply to Thread
Results 1 to 10 of 10

Formula using array to find embedded value

  1. #1
    Ted Horsch
    Guest

    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. #2
    Toppers
    Guest

    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. #3
    Ted Horsch
    Guest

    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. #4
    Toppers
    Guest

    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. #5
    Toppers
    Guest

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

    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. #7
    Toppers
    Guest

    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. #8
    Ted Horsch
    Guest

    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. #9
    Toppers
    Guest

    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. #10
    Toppers
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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