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

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:

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

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

5. ## 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:

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.