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!

 

[Peter_SSs]

uberathlete

It would be very helpful if you were able to post your sheet sample using either Colo’s HTML Maker:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
or Excel jeanie:
http://www.excel-jeanie-html.de/index.php?f=1
rather than an image. That way we would be able to copy your sheet data to our own sheet for testing, rather than having to type out all the data ourselves.

For the highlighting part of your question, you will need to separately use Conditional Formatting. Try this:
1. Select F2:Fxx
2. Format|Conditional Formatting...|Condition 1|Formula is: =ISNA(MATCH(E2+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))|Format...|Patterns|choose colour|OK|OK
Of course you would need to adjust the ranges in the formula to suit your rows of data in columns A:B

Here is a small sample sheet using this CF (without worring about the price because I think you ahve that bit solved already, don't you?)

Excel Workbook
	A	B	C	D	E	F
1	Date	Time	Price		Req Date	Req Price
2	2/01/2000	8:00:20			2/01/2000
3	2/01/2000	8:25:00			3/01/2000
4	3/01/2000	8:00:00			4/01/2000
5	3/01/2000	8:27:05			5/01/2000
6	3/01/2000	9:00:08
7	4/01/2000	8:27:05
8	5/01/2000	8:25:00
9	5/01/2000	8:27:10
10
Prices

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	1. / Formula is =ISNA(MATCH(E2+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))	Abc
F3	1. / Formula is =ISNA(MATCH(E3+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))	Abc
F4	1. / Formula is =ISNA(MATCH(E4+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))	Abc
F5	1. / Formula is =ISNA(MATCH(E5+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))	Abc

 

[uberathlete]

Thanks for the suggestion Peter! I'm still a bit stuck on the part wherein if there is no price at 8:25:00, the price displayed should be the one on the same date closest to but beyond 8:25:00. I have a formula suggested by Domenic that returns the price that occurs on the last hour of the previous date:

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)))

Perhaps I could just modify this formula? Here's the sheet again converted using Excel Jeanie (great idea! certainly saves me the time as well to create the image and upload it ^_^; ) .

Excel Workbook
	A	B	C	D	E	F
1	Date	Time	Price		Req Date	Req Price
2	1/2/2000	8:00:20	99.3		1/2/2000
3	1/2/2000	8:25:00	78.45		1/3/2000
4	1/2/2000	8:33:21	89.2		1/5/2000
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
Sheet1

 

[Peter_SSs]

Try this one. Formula in F2 must be confirmed with Ctrl+Shift+Enter, not just Enter and copied down. Conditional Formatting stays as in my previous suggestion.

BTW: Good to see the Excel jeanie now being used.

Excel Workbook
	A	B	C	D	E	F
1	Date	Time	Price		Req Date	Req Price
2	2/01/2000	8:00:20	1		2/01/2000	2
3	2/01/2000	8:25:00	2		3/01/2000	4
4	3/01/2000	8:00:00	3		4/01/2000	6
5	3/01/2000	8:27:05	4		5/01/2000	7
6	3/01/2000	9:00:08	5
7	4/01/2000	8:27:05	6
8	5/01/2000	8:25:00	7
9	5/01/2000	8:27:10	8
10
Prices

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	1. / Formula is =ISNA(MATCH(E2+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))	Abc

 

[uberathlete]

Peter just wondering, are your dates Day/Month/Year? Cuz when I tried the formula, it gave me an incorrect value. My dates are Month/Day/Year so perhaps that affected it?

 

[Peter_SSs]

Peter just wondering, are your dates Day/Month/Year? Cuz when I tried the formula, it gave me an incorrect value. My dates are Month/Day/Year so perhaps that affected it?

Sorry, I meant to mention that before. My dates are Australian format - which is day/month/year. However, I don't think any change to the formulas should be needed - just use your own dates in your own format and I believe it should work for you.

 

[uberathlete]

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. This is what came out:

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

 

[Domenic]

Maybe...

F2, copied down:

=INDEX($C$2:$C$9,MATCH(E2+"8:25",$A$2:$A$9+$B$2:$B$9)+(LOOKUP(E2+"8:25",$A$2:$A$9+$B$2:$B$9)<>E2+"8:25"))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[uberathlete]

Hi Peter. I tried that formula and it works but the date format does affect the result. When I use a date format of month/day/year, the no 8:25:00 condition doesn't work. I get a #REF. Is there any way to fix this?

 

[uberathlete]

Oh my bad! Domenic, your formula worked. Thank you both for your help! I really appreciate it.