Use XLOOKUP to get largest hour for a given day

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDEFGHIJKLM
1DateTimeOpenHighLowCloseVolumeLU TimeUniqueDateCloseDDesired Result
29/19/20063:00:00147.64147.79147.6147.76389/19/2006#VALUE!145.63
39/19/20064:00:00147.7147.7147.5147.552579/20/2006143.95
49/19/20065:00:00147.53147.53147.31147.452809/24/2006143.95
59/19/20066:00:00147.45147.61147.39147.454389/25/2006
69/19/20067:00:00147.45147.84147.45147.814399/26/2006
79/19/20068:00:00147.78147.99147.68147.959839/27/2006
89/19/20069:00:00147.94147.95147.8147.864649/28/2006
99/19/200610:00:00147.88148.26147.68147.9920559/29/2006
109/19/200611:00:00148.04148.19147.39147.57163610/1/2006
119/19/200612:00:00147.54147.54146.18146.48430210/2/2006
129/19/200613:00:00146.45146.48145.8145.91163810/3/2006
139/19/200614:00:00145.95146.02145.38145.52149110/4/2006
149/19/200615:00:00145.5145.58145.46145.4669110/5/2006
159/19/200616:00:00145.48145.5145.35145.4165010/6/2006
169/19/200617:00:00145.43145.43145.43145.43910/8/2006
179/19/200618:00:00145.5145.5145.4145.41910/9/2006
189/19/200619:00:00145.42145.45145.4145.43110/10/2006
199/19/200620:00:00145.45145.54145.45145.5418010/11/2006
209/19/200621:00:00145.54145.58145.54145.5431510/12/2006
219/19/200622:00:00145.58145.62145.55145.6219010/13/2006
229/19/200623:00:00145.6145.63145.51145.639910/15/2006
239/20/20060:00:00145.55145.55145.54145.54510/16/2006
249/20/20061:00:00145.51145.51145.3145.344010/17/2006
259/20/20062:00:00145.5145.57145.47145.473710/18/2006
269/20/20063:00:00145.51145.51145.15145.1527010/19/2006
279/20/20064:00:00145.17145.17144.55144.8538310/20/2006
289/20/20065:00:00144.84144.94144.7144.916810/26/2006
299/20/20066:00:00144.9144.91144.73144.7316510/27/2006
309/20/20067:00:00144.7144.73144.41144.5616510/29/2006
319/20/20068:00:00144.58144.85144.53144.8532710/30/2006
329/20/20069:00:00144.84144.87144.63144.844810/31/2006
339/20/200610:00:00144.8145.1144.53144.72108111/1/2006
349/20/200611:00:00144.7144.9144.44144.848511/2/2006
359/20/200612:00:00144.8145.38144.77145.3851111/3/2006
369/20/200613:00:00145.1145.35144.5144.5513111/5/2006
379/20/200614:00:00144.6144.7143.85143.9530311/6/2006
389/24/200618:00:00144.05144.1143.95143.9931211/7/2006
399/24/200619:00:00143.97144.12143.92143.9858611/8/2006
409/24/200620:00:00144144.01143.79143.8376711/9/2006
419/24/200621:00:00143.9143.9143.74143.8441111/10/2006
429/24/200622:00:00143.81143.9143.79143.929411/12/2006
439/24/200623:00:00143.9143.97143.89143.9520811/13/2006
CLCont1hr
Cell Formulas
RangeFormula
J2:J4573J2=UNIQUE(A2:A88943)
K2K2=XLOOKUP(I2,$B$2:B88943,XLOOKUP(J2,A2:A88943,F2:F88943))
Dynamic array formulas.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,510
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=INDEX(SORT(FILTER($B$2:$F$1000,$A$2:$A$1000=J2),1,-1),1,5)
 
Solution

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
How about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,510
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,175,524
Messages
5,897,923
Members
434,687
Latest member
alybazaza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top