Lookup function to find first instance above current row

[ddoublev]

Hi,

I'm hoping someone may have an easy solution for this.

Below is a sample from a sheet I am working with. What I need to be able to do is look upwards from a row to find the FIRST instance of a value in order to calculate the number of days since the last reset.

Below is the formula I have been working with, however it only looks from most bottom row to top. What I need is to lookup from the row containing the formula and treat this row as the "bottom" row. (ie Formula in row 10 to look up from row 10)

=TODAY()-LOOKUP(2,1/([Stage 1 Startup Reset]=1),[Date])

Is this possible?

[TABLE="width: 421"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date
[/TD]
[TD]Stage 1 Startup Reset
[/TD]
[TD]Stage 1 Days since reset
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]19/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]20/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]21/05/2017
[/TD]
[TD]1
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]22/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]23/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]24/05/2017
[/TD]
[TD]1[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]25/05/2017
[/TD]
[TD]0[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]26/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]27/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]28/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]29/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]30/05/2017
[/TD]
[TD]0[/TD]
[TD]-6
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]31/05/2017
[/TD]
[TD]0
[/TD]
[TD]-6
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance

 

[Aladin Akyurek]

What result should obtain given the sample you posted?

 

[wideboydixon]

I'm guessing a little here but perhaps:

Excel 2016 (Windows) 32 bit
	A	B	C
1	Date	Stage 1 Startup Reset	Stage 1 Days since reset
2	19/05/2017	0	0
3	20/05/2017	0	1
4	21/05/2017	1	2
5	22/05/2017	0	1
6	23/05/2017	0	2
7	24/05/2017	1	3
8	25/05/2017	0	1
9	26/05/2017	0	2
10	27/05/2017	0	3
11	28/05/2017	0	4
12	29/05/2017	0	5
13	30/05/2017	1	6
14	31/05/2017	0	1
Sheet1

Cell Formulas
Range	Formula
C2	=IFERROR($A2-LOOKUP(2,1/($B$1:$B1=1),$A$1:$A1),$A2-$A$2)

WBD

 

[ddoublev]

Hi,
Thanks for your responses
I have succesfully worked the issue out with the following formula

=IF((A6-LOOKUP(2,1/(B$2:B6=1),A6))<0, NA(), (A6-LOOKUP(2,1/(B$2:B6=1),A6))) returns a value of 2.

Thanks for your help