Pick the first value from a range of data which is more than X value

[gnaga]

Book1
	E	F	G	H	I
6
7		10:00:00 AM
8
9
10
11
12		10:00:00 AM
13		10:06:00 AM
14		10:06:00 AM
15		10:12:00 AM
16		10:12:00 AM
17		10:18:00 AM
18		10:18:00 AM
19		10:24:00 AM
20		10:24:00 AM
21		10:27:00 AM
22		10:27:00 AM
23		10:30:00 AM
24		10:30:00 AM
25		10:42:00 AM
26		10:42:00 AM
27		10:54:00 AM
28
29
30
31
32		10:54:00 AM
33		11:00:00 AM
34		11:00:00 AM
35		11:06:00 AM
36
Sheet1

Cell Formulas
Range	Formula
H21	H21	=INDEX(F8:F35,MATCH(TRUE,INDEX(F8:F35>(F7+(60/1440)),0),0))

Formula in the cell H21 returns blank cell. I wish to pick the time which is just crossing more than an hour than "F7". The correct result is "F35" should return. I am finding it difficult to get this correct. Please help.

 

[Peter_SSs]

Little starge behavior when I change the data value. Column "N" having two identical values both get formatted.

My previous formula accounted for two or more identical values (eg see your column F) but I only allowed for that in consecutive cells.
Try this formula instead

=AND(E7=AGGREGATE(15,6,E$8:E$34/(E$8:E$34>E$6+1/24),1),ISNA(MATCH(E7,E$6:E6,0)))

Moreover if you could explain your formula would be grateful.

=AND(E7=AGGREGATE(15,6,E$8:E$34/(E$8:E$34>E$6+1/24),1),ISNA(MATCH(E7,E$6:E6,0)))

For a cell to be formatted ..
- the red part says that it must equal the smallest value that is more than 1 hour after row 6 (in your latest sample N26 and N31 meet that criteria)
- the blue part says that there must be no value the same above (that rules out N31)

 

[gnaga]

My previous formula accounted for two or more identical values (eg see your column F) but I only allowed for that in consecutive cells.
Try this formula instead

=AND(E7=AGGREGATE(15,6,E$8:E$34/(E$8:E$34>E$6+1/24),1),ISNA(MATCH(E7,E$6:E6,0)))

=AND(E7=AGGREGATE(15,6,E$8:E$34/(E$8:E$34>E$6+1/24),1),ISNA(MATCH(E7,E$6:E6,0)))

For a cell to be formatted ..
- the red part says that it must equal the smallest value that is more than 1 hour after row 6 (in your latest sample N26 and N31 meet that criteria)
- the blue part says that there must be no value the same above (that rules out N31)

Thank you so much Peter_SSs ????. I understand the formula what it does. It may take years for me to think and build formula like this. Is there any specific approach to build the formula??

 

[Peter_SSs]

Thank you so much Peter_SSs

You're welcome.

Is there any specific approach to build the formula?

I don't think so, it just comes with experience. ?