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?

Time		Action	Price
00:00:01.00		Buy
00:00:01.65		Buy
00:03:08.00		Buy

Date	Time	Price
19/12/2018	00:00:00.37	1.13519
19/12/2018	00:00:00.95	1.13484
19/12/2018	00:00:02.89	1.13484
19/12/2018	00:00:08.14	1.13484
19/12/2018	00:00:14.78	1.13484
19/12/2018	00:00:41.66	1.13485
19/12/2018	00:00:58.03	1.13486
19/12/2018	00:00:58.72	1.13485
19/12/2018	00:01:00.14	1.13484
19/12/2018	00:01:01.79	1.13487
19/12/2018	00:01:04.05	1.13486
19/12/2018	00:01:04.66	1.13485
19/12/2018	00:01:18.21	1.13484
19/12/2018	00:01:21.27	1.13485
19/12/2018	00:01:37.06	1.13485
19/12/2018	00:02:37.24	1.13485
19/12/2018	00:02:37.90	1.13484
19/12/2018	00:02:40.99	1.13485
19/12/2018	00:03:07.69	1.13485
19/12/2018	00:03:08.01	1.13485
19/12/2018	00:03:28.09	1.13485

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))