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.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Dates[/TD]
[TD]1/1/2017[/TD]
[TD]2/1/2017[/TD]
[TD]3/1/2017[/TD]
[TD]4/1/2017[/TD]
[TD]5/1/2017[/TD]
[TD]6/1/2017[/TD]
[TD]7/1/2017[/TD]
[TD]8/1/2017[/TD]
[/TR]
[TR]
[TD]Values[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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