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

1. 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 !  Reply With Quote

2. 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:   Reply With Quote

3. 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  Reply With Quote

4. 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))))  Reply With Quote

5. Re: 2nd Occurrence index match on dates Originally Posted by sadboy309 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  Reply With Quote

User Tag List

Tags for this Thread

6/1/2017, date, formula, return, values  Posting Permissions

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