Results 1 to 5 of 5

Thread: 2nd Occurrence index match on dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 2nd Occurrence index match on dates

    Hi all,

    I have two rows:
    - A date row
    - A value row

    I would like to retrieve a date where the corresponding 'value' is 0. However, it is possible that the first 3 values are 0, then 50, 30 and then 0 again. The formula should always return the date when a 0 follows after values > 0.


    Dates 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017
    Values 0 0 0 50 30 0 0 0

    In the table above, the formula should return 6/1/2017. It is the first date that corresponds to value 0, following after some other positive values.

    If someone could enlighten me with new formulas that would be awesome !

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd Occurrence index match on dates

    Formula is (remember Ctrl+Shift+Enter end line):

    Code:
    {=IFERROR(INDEX($B$1:$I$1,MATCH(1,(B2:I2>0)*(C2:J2=0),0)+1),"")}
    As Picture:


  3. #3
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd Occurrence index match on dates

    Hi,

    Based on your data residing in range A1:I2, try:

    Code:
    =INDEX(B1:I1,MATCH(2,1/(B2:I2<>0))+1)
    Array entered, i.e. with CTRL+SHIFT+ENTER.

    Note: This solution will always return the first date AFTER the last value <>0. If the last date in the range has a value <>0, then an error will occur (appropriate handling should then be applied).

    Matty

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd Occurrence index match on dates

    I found 1 formula new, I share us know now:

    Formula:
    Code:
    =INDEX(B1:I1,LOOKUP(9E+307,(COLUMN(B1:I1)/(B2:I2<>0))))

  5. #5
    New Member
    Join Date
    Jan 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd Occurrence index match on dates

    Quote Originally Posted by sadboy309 View Post
    Formula is (remember Ctrl+Shift+Enter end line):

    Code:
    {=IFERROR(INDEX($B$1:$I$1,MATCH(1,(B2:I2>0)*(C2:J2=0),0)+1),"")}
    As Picture:


    This worked like a charm. Could you explain how the formula 'knows' that it should take the 2nd occurrence. I try to understand how the different parts solve my problem.

    Thanks for your effort

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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