Conditional Lookup? prt2

[uberathlete]

Hi everyone, I need some help. I posed a similar question 2 days ago (http://www.mrexcel.com/board2/viewtopic.php?t=284838&highlight=) but this one's a bit different. 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 price of the the same date at the hour closest to but beyond 8:25:00 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 price on the same date at the hour closest to but beyond 8:25:00 which is 99.99 (occuring at 9:10:00) and must be highlighted as well.

Anyone know how I could do this? Oh also, for my earlier question (please see link above), the suggestion by Domenic worked great but there was no highlighting that occured. So I'd like to modify the formula he suggested to accomodate this feature, if it's possible.

Any suggestions would be greatly appreciated. Thank you!

 

[Yogi Anand]

Hi uberathlete:

This is what I came up with ...

Book3
	A	B	C	D	E	F
1	Date	Time	Price		Date	Price
2	1/2/2000	8:00:20	99.3		1/2/2000	78.45
3	1/2/2000	8:25:00	78.45		1/3/2000	54.54
4	1/2/2000	8:33:21	89.2		1/5/2000	99.99
5	1/2/2000	9:42:00	99.09
6	1/2/2000	10:00:00	90
7	1/3/2000	8:00:00	43.89
8	1/3/2000	8:14:00	56.78
9	1/3/2000	8:25:00	54.54
10	1/3/2000	11:00:00	77.89
11	1/5/2000	8:10:00	94
12	1/5/2000	9:10:00	99.99
13	1/5/2000	10:10:00	132.76
14	1/5/2000	11:10:00	80
15
Sheet5

formula in cell F2 is ...

=INDEX(C:C,MIN(IF($A$2:$A$14=E2,IF($B$2:$B$14>=("8:25"+0),ROW($B$2:$B$14)))))

this is then copied down.

 

[Peter_SSs]

Hi Peter. I tried the formula first using your dates and prices which did work but when I changed the prices, so did the values.

Yes, looking at my suggestion on a fresh day indicates that it had no chance of being correct. Sorry for heading you in the wrong direction.

Given that your dates and times are in order, here is another try.

Excel Workbook
	A	B	C	D	E	F
1	Date	Time	Price		Req Date	Req Price
2	02/01/2000	8:00:20	435		02/01/2000	54
3	02/01/2000	8:25:00	54		03/01/2000	565
4	03/01/2000	8:00:00	3		04/01/2000	6
5	03/01/2000	8:27:05	565		05/01/2000	765
6	03/01/2000	9:00:08	353
7	04/01/2000	8:27:05	6
8	05/01/2000	8:25:00	765
9	05/01/2000	8:27:10	8
10
Prices(2)