• 546K Members
• 2,528 Online
• 652K Conversations
SOLVED

New Contributor

# Using index match offset to find first date that amounts drop to zero

Hi,

Trying to build a formula that returns the first date that a series of amounts ceases, ie drops to zero, using an index on date range, and attempting to match where amount is zero and amount of month prior is >0.

Importantly, the series of payments may not begin in first month, so no value in just finding the first zero amount...

My formula currently is CSE but returning #n/a:

{=INDEX(C1:F1,,MATCH(TRUE,(C2:F2=0)*(OFFSET(C2:F2,0,-1)>0),0))}

Cells B1..F1 are a range of 5 dates from 1 Jan to 1 May

Cells B2..F2 are amounts 0,0,500,500,0

 Month 1-Jan 1-Feb 1-Mar 1-Apr 1-May Drop off date PX \$ 0 0 500 500 0 #N/A

In the above case formula should be returning "1 May", as when series first dropped to zero.

3 Replies
Highlighted
Solution

# Re: Using index match offset to find first date that amounts drop to zero

In the attached file, the formula in H2 is:

=INDEX(B1:F1,
MATCH(LOOKUP(2,1/(B2:F2>0),B1:F1),
B1:F1,0)+1)

Highlighted

# Re: Using index match offset to find first date that amounts drop to zero

Thanks @Twifoo - works perfectly, cheers.

Highlighted

# Re: Using index match offset to find first date that amounts drop to zero

You're very much welcome!

Related Conversations
Date format in Stream
Dave Pyett in Microsoft Stream Forum on
3 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Totaling using two sets of data
dazedandconfused in Excel on
2 Replies
Matching Excel cells by column
ddelise in Excel on
3 Replies