Data separation with referance to 'Time'

Enclosed sample excel.

In col A to E, I am getting data from Google finance.

In G4, G5, G6, it's auto-generated time values changing everyday.

I wish to split the data with reference to those time values as shown in sample excel.

Can anyone please help on this ?

Thanking in advance.

@ Richard Buttrey

Sorry to say, but this formula can give the value corresponding to only one time value i.e. G4
I am expecting the range starting from A4 to the value corresponding to G4. (as shown in result in sample sheet)
Anyway, thanks a lot for your kind efforts.

ARRAY formulas in G11 and M11
In G11

=IFERROR(INDEX($A$4:$A$3763,SMALL(IF((ROUND(MOD($A$4:$A$3763,1),7)>=ROUND(G$9,7))*(ROUND(MOD($A$4:$A$3763,1),7)<=ROUND(I$9,7)),ROW($A$4:$A$3763),""),ROWS(G$11:G11))-ROW($A$4)+1),"")

In M11

=IFERROR(INDEX($A$4:$A$3763,SMALL(IF((ROUND(MOD($A$4:$A$3763,1),7)>=ROUND(M$9,7))*(ROUND(MOD($A$4:$A$3763,1),7)<=ROUND(O$9,7)),ROW($A$4:$A$3763),""),ROWS(M$11:M11))-ROW($A$4)+1),"")

then drag down.

in H11 then drag across

=IF($G11="","",INDEX(B$4:B$3763,MATCH($G11,$A$4:$A$3763,0)))

in N11 then dragacross

=IF($M11="","",INDEX(B$4:B$3763,MATCH($M11,$A$4:$A$3763,0)))

ARRAY formulas should be confirmed with CTrl+Shift+Enter Keys

@ kvsrinivasamurthy ,

Yes, this formula worked, but the problem is, when drag across, it starts with initial values again after time value in G4

Pl veify ARRAY formulas are entered correctly.Confirmed with Ctrl+Shift+Enter.
In case still problem is there upload the file showing the problem.

@ kvsrinivasamurthy ,
Enclosed, the sample excel you have sent...
after drag across, pl see after 10:36, it again gives 9:15, 9:16....
and after 11:57, it again repeats 10:37, 10:38.....

Instead, I am uploading sample 2 excel.

Here, the date / time format is exactly same in Col A and col G

The end result desired is to get OPEN / HIGH / LOW / CLOSE values of a "RANGE"

for ex, consider first range starting from 12-06-2017 9:15:00 AM (A4) to 12-06-2017 10:36:00 AM (first value in G4)

For this range, open is B4, High is =MAX(C4:C85), Low is =MIN(D4:D85) and close is E85

Since, the values in col G will be always changing, so those ranges too.

Please see if this is possible using any formulas ?

Thanking in advance.

Do you want result as in Sample2 file H to K column.

@ kvsrinivasamurthy ,

Yes Sir, I would be highly grateful to you if you could help on this.
I am looking for result as shown in excel sample 2 , H to K column.

Pl see the changes in the formula table.
In I1
=INDEX(B$4:B$3763,MATCH($G4,$A$4:$A$3763,1))
In J1
=MAX(INDEX($C$4:$C$3763,MATCH($G4,$A$4:$A$3763,1)):INDEX($C$4:$C$3763,MATCH($H4,$A$4:$A$3763,1)))
In K1
=MIN(INDEX($D$4:$D$3763,MATCH($G4,$A$4:$A$3763,1)):INDEX($D$4:$D$3763,MATCH($H4,$A$4:$A$3763,1)))
In L1
=INDEX(E$4:E$3763,MATCH($H4,$A$4:$A$3763,1))
then drag down all.

@ kvsrinivasamurthy ,

Thanks a lot Sir.
It's a great help for me.

welcome.Pl mark the thread solved.

@ kvsrinivasamurthy ,

Sir, I have marked the thread as 'solved'.
However, there is a small problem...
I don't know whether it's right to ask here itself OR start a new thread.
I have enclosed the sample excel that you had sent.
In col H, I need the time values in between 9:15 to 15:30 for each days...including 15:30

and then to be used in col G as you had added TIMEVALUE("0:01:00")

Can you please help on this ?

Helper columns K , L & M are used .Pl see file.

ARRAY formulas below to be confirmed with Ctrl+Shift+Enter keys
In G13 , the drag down

=IFERROR(INDEX($K$13:$M$19,IF(OR(ROUND(MOD($H12,1),6)=ROUND($L$10,6),$H12=""),1,SUMPRODUCT(--(FLOOR($H$12:$H12,1)=FLOOR($H12,1)))+1),SUMPRODUCT(--(ROUND(MOD($H$12:$H12,1),6)=ROUND($L$10,6)))+1)+TIMEVALUE("0:01"),"")

In H13 , the drag down

=IFERROR(INDEX($K$13:$M$19,IF(OR(ROUND(MOD($H12,1),6)=ROUND($L$10,6),$H12=""),2,SUMPRODUCT(--(FLOOR($H$12:$H12,1)=FLOOR($H12,1)))+2),SUMPRODUCT(--(ROUND(MOD($H$12:$H12,1),6)=ROUND($L$10,6)))+1),"")

Sorry wrong file attached. Now correct file attached. Pl see.