Finding a value by date and time. Quickly without looping.

[p45cal]

What you're calling raw data and what I call raw data is a bit different - I'd like to know how you get your raw data into Excel in the first place!, but I'll look into how I might extract the necessary value from what you're calling raw data tomorrow.

[Mellstock]

Thanks to viewing a few you tube videos I have managed to get nearer to solving this issue using Index and Match formulas in a spreadsheet. (attached) So I know Excel can do this.

How to get a vba programatic solution is my next step.

Thanks for all the help so far. Any pointers on achieving a vba solution will be greatly appreciated.

[p45cal]

You can do this from your raw data sheet directly, that way (a) you don't have to clean and reorganise that data at all and (b) you'll only have to match/lookup one column.

Let's say you have you granularity value in a cell, say K4, a number in minutes, so 1, 5 10, 12, 20, 30, whatever.
We'll have a regular excel date in cell K5.
We can derive a string to match thus:
=TEXT(K5+TIME(22,IF(K4>1,-K4,0),0),"yyyy:mm:dd-hh:mm:ss")
You don't need this formula in a cell anywhere but it will show you how it works and you can experiment changing the values in K4 and K5.
Instead incorporate it into a larger formula:
=VLOOKUP(TEXT(K5+TIME(22,IF(K4>1,-K4,0),0),"yyyy:mm:dd-hh:mm:ss"),$A$1:$E$30,5,FALSE)
The $A$1:$E$30 reference is for your raw data sheet (Sample)
You can apply a rounding function around the whole lot of course.
The equivalent index/match formula would be:
=INDEX($E$1:$E$30,MATCH(TEXT(K5+TIME(22,IF(K4>1,-K4,0),0),"yyyy:mm:dd-hh:mm:ss"),$A$1:$A$30,0))
again the $E$1:$E$30 and $A$1:$A$30 are references to cells on the Sample sheet of the file you attached to msg#14 (whose times are out of range for this formula).

vba tomorrow

[Mellstock]

Thank you so much for this p45cal. I have played around with the VLOOKUP version and altered K4,K5 and the hard coded 22 in the code. All works a treat.