Use XLOOKUP to get largest hour for a given day

[GeeWhiz7]

Hi folks,
Struggling to make the following work, mainly because the last hour is not always the same.

• I have 1 hour Close (col F) data for a continuous futures contract
• Due to when markets trade, the final hour of data is not always the same. In this case it is not always 23:00:00 time.
• I am trying to make a "Daily" close table that takes the last time (whether 23:00 or 14:00) etc for each unique day and the last time recorded.
• I've tried a few lookups and nested xlookups, but I can't figure out how to get the close price for the max/last time for the given day.

Mini sheet is below. Your expert help is appreciated as I'm stuck.

CLCont1hr.txt
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Time	Open	High	Low	Close	Volume		LU Time	UniqueDate	CloseD		Desired Result
2	9/19/2006	3:00:00	147.64	147.79	147.6	147.76	38			9/19/2006	#VALUE!		145.63
3	9/19/2006	4:00:00	147.7	147.7	147.5	147.55	257			9/20/2006			143.95
4	9/19/2006	5:00:00	147.53	147.53	147.31	147.45	280			9/24/2006			143.95
5	9/19/2006	6:00:00	147.45	147.61	147.39	147.45	438			9/25/2006
6	9/19/2006	7:00:00	147.45	147.84	147.45	147.81	439			9/26/2006
7	9/19/2006	8:00:00	147.78	147.99	147.68	147.95	983			9/27/2006
8	9/19/2006	9:00:00	147.94	147.95	147.8	147.86	464			9/28/2006
9	9/19/2006	10:00:00	147.88	148.26	147.68	147.99	2055			9/29/2006
10	9/19/2006	11:00:00	148.04	148.19	147.39	147.57	1636			10/1/2006
11	9/19/2006	12:00:00	147.54	147.54	146.18	146.48	4302			10/2/2006
12	9/19/2006	13:00:00	146.45	146.48	145.8	145.91	1638			10/3/2006
13	9/19/2006	14:00:00	145.95	146.02	145.38	145.52	1491			10/4/2006
14	9/19/2006	15:00:00	145.5	145.58	145.46	145.46	691			10/5/2006
15	9/19/2006	16:00:00	145.48	145.5	145.35	145.41	650			10/6/2006
16	9/19/2006	17:00:00	145.43	145.43	145.43	145.43	9			10/8/2006
17	9/19/2006	18:00:00	145.5	145.5	145.4	145.4	19			10/9/2006
18	9/19/2006	19:00:00	145.42	145.45	145.4	145.4	31			10/10/2006
19	9/19/2006	20:00:00	145.45	145.54	145.45	145.54	180			10/11/2006
20	9/19/2006	21:00:00	145.54	145.58	145.54	145.54	315			10/12/2006
21	9/19/2006	22:00:00	145.58	145.62	145.55	145.62	190			10/13/2006
22	9/19/2006	23:00:00	145.6	145.63	145.51	145.63	99			10/15/2006
23	9/20/2006	0:00:00	145.55	145.55	145.54	145.54	5			10/16/2006
24	9/20/2006	1:00:00	145.51	145.51	145.3	145.34	40			10/17/2006
25	9/20/2006	2:00:00	145.5	145.57	145.47	145.47	37			10/18/2006
26	9/20/2006	3:00:00	145.51	145.51	145.15	145.15	270			10/19/2006
27	9/20/2006	4:00:00	145.17	145.17	144.55	144.85	383			10/20/2006
28	9/20/2006	5:00:00	144.84	144.94	144.7	144.9	168			10/26/2006
29	9/20/2006	6:00:00	144.9	144.91	144.73	144.73	165			10/27/2006
30	9/20/2006	7:00:00	144.7	144.73	144.41	144.56	165			10/29/2006
31	9/20/2006	8:00:00	144.58	144.85	144.53	144.85	327			10/30/2006
32	9/20/2006	9:00:00	144.84	144.87	144.63	144.8	448			10/31/2006
33	9/20/2006	10:00:00	144.8	145.1	144.53	144.72	1081			11/1/2006
34	9/20/2006	11:00:00	144.7	144.9	144.44	144.8	485			11/2/2006
35	9/20/2006	12:00:00	144.8	145.38	144.77	145.38	511			11/3/2006
36	9/20/2006	13:00:00	145.1	145.35	144.5	144.55	131			11/5/2006
37	9/20/2006	14:00:00	144.6	144.7	143.85	143.95	303			11/6/2006
38	9/24/2006	18:00:00	144.05	144.1	143.95	143.99	312			11/7/2006
39	9/24/2006	19:00:00	143.97	144.12	143.92	143.98	586			11/8/2006
40	9/24/2006	20:00:00	144	144.01	143.79	143.83	767			11/9/2006
41	9/24/2006	21:00:00	143.9	143.9	143.74	143.84	411			11/10/2006
42	9/24/2006	22:00:00	143.81	143.9	143.79	143.9	294			11/12/2006
43	9/24/2006	23:00:00	143.9	143.97	143.89	143.95	208			11/13/2006
CLCont1hr

Cell Formulas
Range	Formula
J2:J4573	J2	=UNIQUE(A2:A88943)
K2	K2	=XLOOKUP(I2,$B$2:B88943,XLOOKUP(J2,A2:A88943,F2:F88943))
Dynamic array formulas.

 

[Fluff]

How about

=INDEX(SORT(FILTER($B$2:$F$1000,$A$2:$A$1000=J2),1,-1),1,5)

 

[GeeWhiz7]

How about

=INDEX(SORT(FILTER($B$2:$F$1000,$A$2:$A$1000=J2),1,-1),1,5)

It will take me at least 10 min to figure out how this syntax works, but it works.
Thank you Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.