Match closest time

[ai225]

Hello,

I have a list of stocks that were bought at a specific time but i don't know the price. I have a list of prices by time but it may differs from the time of action (differ in ms). Hoe can i match it to the closest time please in order to find the price?

[TABLE="width: 316"]
[TR]
[TD]Time[/TD]
[TD] [/TD]
[TD]Action[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD="align: right"]00:00:01.00[/TD]
[TD] [/TD]
[TD]Buy[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]00:00:01.65[/TD]
[TD] [/TD]
[TD]Buy[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]00:03:08.00[/TD]
[TD] [/TD]
[TD]Buy[/TD]
[TD] [/TD]
[/TR]
[/TABLE]

[TABLE="width: 230"]
[/TABLE]
[TABLE="width: 230"]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[/TABLE]
[TABLE="width: 241"]
[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:00:00.37[/TD]
[TD="align: right"]1.13519[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:00:00.95[/TD]
[TD="align: right"]1.13484[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:00:02.89[/TD]
[TD="align: right"]1.13484[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:00:08.14[/TD]
[TD="align: right"]1.13484[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:00:14.78[/TD]
[TD="align: right"]1.13484[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:00:41.66[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:00:58.03[/TD]
[TD="align: right"]1.13486[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:00:58.72[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:01:00.14[/TD]
[TD="align: right"]1.13484[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:01:01.79[/TD]
[TD="align: right"]1.13487[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:01:04.05[/TD]
[TD="align: right"]1.13486[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:01:04.66[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:01:18.21[/TD]
[TD="align: right"]1.13484[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:01:21.27[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:01:37.06[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:02:37.24[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:02:37.90[/TD]
[TD="align: right"]1.13484[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:02:40.99[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:03:07.69[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:03:08.01[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[TR]
[TD="align: right"]19/12/2018[/TD]
[TD="align: right"]00:03:28.09[/TD]
[TD="align: right"]1.13485[/TD]
[/TR]
[/TABLE]


Many Thanks

 

[khandelwal]

If you want to group them into Minutes, use formula "=minute" to group them from milliseconds to minutes. same can be done with =hour as well if grouping needs to be done on hours basis.

 

[steve the fish]

Maybe something like:

=INDEX($C$2:$C$22,MATCH(G2,$B$2:$B$22))