Lookup a value after condition met

[henryvii99]

Hello,

I would like to lookup a value in specific column after condition is met
In my case, I want to report the time on row 1 after the time specified in table 2

Here's my example data:

Time	Price1	Price2
18/5/2018 10:25	405	10
18/5/2018 10:27	400	20
18/5/2018 10:29	395	30
18/5/2018 10:31	390	40
18/5/2018 10:33	400	50
18/5/2018 10:35	410	45
18/5/2018 10:37	420	35
18/5/2018 10:39	430	65

<tbody>
</tbody>

Here's the control table, the specified time for table lookup is different for each column

	Price 1	Price 2
Start to count	10:27	10:33
Price at counting	400	50
Min. price	380	40
Max. price	420	60
Time when price below min.	A	C
Time when price reach max.	B	D

<tbody>
</tbody>

The expected values are:

A: N/A (as it never fall below min. price)
B: 10:37
C: 10:37 (not 10:29 as it does not reach specific time yet)
D: 10:39

I think I can use index to find my values, but the problem is I will only look for values after the specific time

Any help is much appreciated, many thanks for your help!

 

[Peter_SSs]

Try these, copied across.

Excel Workbook
	A	B	C
1	Time	Price1	Price2
2	18/05/2018 10:25	405	10
3	18/05/2018 10:27	400	20
4	18/05/2018 10:29	395	30
5	18/05/2018 10:31	390	40
6	18/05/2018 10:33	400	50
7	18/05/2018 10:35	410	45
8	18/05/2018 10:37	420	35
9	18/05/2018 10:39	430	65
10
11		Price 1	Price 2
12	Start to count	10:27	10:33
13	Price at counting	400	50
14	Min. price	380	40
15	Max. price	420	60
16	Time when price below min.	N/A	10:37
17	Time when price reach max.	10:37	10:39
Times

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​
1​	Time	Price1	Price2			Price 1	Price 2
2​	5/18/2018 10:25​	405​	10​		Start to count	10:27​	10:33​
3​	5/18/2018 10:27​	400​	20​		Price at counting	400​	50​
4​	5/18/2018 10:29​	395​	30​		Min. price	380​	40​
5​	5/18/2018 10:31​	390​	40​		Max. price	420​	60​
6​	5/18/2018 10:33​	400​	50​		Time when price below min.	#NUM!​	10:37​
7​	5/18/2018 10:35​	410​	45​		Time when price reach max.	10:37​	10:39​
8​	5/18/2018 10:37​	420​	35​
9​	5/18/2018 10:39​	430​	65​

Sheet: Sheet56

Array formula in F6:
=INDEX($A$2:$A$9,SMALL(IF(((A2:A9-INT(A2:A9))>=F2)*(B2:B9<=F4),MATCH(ROW(A2:A9),ROW(A2:A9)),""),1))

Array formula in F7:
=INDEX($A$2:$A$9,SMALL(IF(((A2:A9-INT(A2:A9))>=F2)*(B2:B9>=F5),MATCH(ROW(A2:A9),ROW(A2:A9)),""),1))

Array formula in G6:
=INDEX($A$2:$A$9,SMALL(IF(((A2:A9-INT(A2:A9))>=G2)*(C2:C9<=G4),MATCH(ROW(A2:A9),ROW(A2:A9)),""),1))

Array formula in G7:
=INDEX($A$2:$A$9,SMALL(IF(((A2:A9-INT(A2:A9))>=G2)*(C2:C9>=G5),MATCH(ROW(A2:A9),ROW(A2:A9)),""),1))

 

[henryvii99]

Dear Peter_SSs,

Many, many thanks for your prompt reply!

However, as I copied these to excel 2007, it doesn't seem to work
B16 contains error and doesn't allow me to proceed, and B17 report as N/A


For B16, maybe it is

=IFERROR(AGGREGATE(15,6,MOD($A$2:$A$9,1)/((MOD($A$2:$A$9,1)>=B12)*(B$2:B$9<=B14)),1),"N/A")

I think we have to compare the below min cell at B14. Still it will report as N/A. Maybe my version is too old? Or matching time is a bit tricky?

 

[Peter_SSs]

However, as I copied these to excel 2007, it doesn't seem to work

It won't - the AGGREGATE function was only introduced in Excel 2010.
For my layout, try these. They are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formulas can then be copied across.

Excel Workbook
	A	B	C
1	Time	Price1	Price2
2	18/05/2018 10:25	405	10
3	18/05/2018 10:27	400	20
4	18/05/2018 10:29	395	30
5	18/05/2018 10:31	390	40
6	18/05/2018 10:33	400	50
7	18/05/2018 10:35	410	45
8	18/05/2018 10:37	420	35
9	18/05/2018 10:39	430	65
10
11		Price 1	Price 2
12	Start to count	10:27	10:33
13	Price at counting	400	50
14	Min. price	380	40
15	Max. price	420	60
16	Time when price below min.	N/A	10:37
17	Time when price reach max.	10:37	10:39
Times (2)

 

[henryvii99]

Dear Peter and Oscar,

Many thanks for your kindness!
It worked like a charm

 

[Peter_SSs]

Dear Peter and Oscar,

Many thanks for your kindness!
It worked like a charm

Good news. Thanks for letting us know.