+ Reply to Thread
Results 1 to 4 of 4

search for text in colB, starting with next row search for 1st occurrence of specific #

  1. #1
    Registered User
    Join Date
    07-14-2010
    Location
    Hot Springs, AR
    MS-Off Ver
    2010
    Posts
    38

    search for text in colB, starting with next row search for 1st occurrence of specific #

    I want to search for DG in colB, then beginning with the next row search for the first occurrence of 123 in colA and then return 456 in colC.

    I've tried various sumproduct formulae with no luck, i.e. =SUMPRODUCT(--(B5:B200="DG"),--(A5:A200=165),--C5:C200)

    Here's my sample data:

    colA----colB-----colC
    ---------DF
    106--------------229
    123--------------444
    ---------DG
    117--------------678
    123--------------456
    152--------------100
    286--------------750
    123--------------111
    ---------KL
    123--------------677


    Thanks,
    roothog
    Last edited by roothog; 08-24-2012 at 10:41 PM.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: search for text in colB, starting with next row search for 1st occurrence of specific

    With your sample data in A1:C12
    and
    D1: a Col_B value to find.....DG
    D2: a Col_A number fo find...123

    This regular formula finds the Col_C value that corresponds to the first Col_A instance of the D2 value that occurs AFTER the first Col_B instance of the D1 value
    Please Login or Register  to view this content.
    In the above example, the formula returns: 456
    If you change D1 to "KL", the formula returns: 677

    Is that something you can work with
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: search for text in colB, starting with next row search for 1st occurrence of specific

    try
    =INDEX(C:C,MATCH("dg",B:B,0)+MATCH(123,OFFSET(B1,MATCH("dg",B:B,0),-1,10000),0))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    07-14-2010
    Location
    Hot Springs, AR
    MS-Off Ver
    2010
    Posts
    38

    Re: search for text in colB, starting with next row search for 1st occurrence of specific

    Wow! First to Ron - That is definitely something I can work with AND...it works like a charm!
    Next to Martin in UK...yours works perfectly as well.

    And I thank you both for such quick responses!

    Now to figure how to post this as solved!!!

+ 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