Conditional Lookup?

[uberathlete]

Hi everyone, I really need some help. Here's the situation (please refer to image):

Basically, I would like to fill column F. The values in column F2 must be those prices that occured at 8:25:00 on the corresponding date specified in column E. However, when there is no 8:25:00 price , then the last price of the previous date must be used instead and this value should be highlighted.

So in this case, cell F requires the 8:25:00 price on January 1, 2000 which would be 78.45 coming from cell C3. For cell F4, there is no 8:25:00 price on January 5, 2000 so instead it should have the last price of the previous date which is 77.89 and must be highlighted as well.

The result should then be like this:

Anyone know how I could do this? Any suggestions would be greatly appreciated. Thank you!

 

[Domenic]

Since the data is sorted by date and time, in ascending order, try the following...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try...

F2, copied down:

=LOOKUP(BigNum,CHOOSE({1,2},INDEX($C$2:$C$14,MATCH(E2-1,$A$2:$A$14)),VLOOKUP("8:25:00"+0,INDEX($B$2:$B$14,MATCH(E2,$A$2:$A$14,0)):INDEX($C$2:$C$14,MATCH(E2,$A$2:$A$14)),2,0)))

Hope this helps!

 

[uberathlete]

Thanks, I'll try that.