2nd Occurrence index match on dates

[Henk2017]

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

<tbody>
</tbody>

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 !

 

[sadboy309]

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

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

As Picture:

 

[Matty]

Hi,

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

=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

 

[sadboy309]

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

Formula:

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

 

[Henk2017]

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

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