Conditional Lookup? prt2

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
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):

6c3397p.jpg



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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1DateTimePriceReq DateReq Price
22/01/20008:00:202/01/2000
32/01/20008:25:003/01/2000
43/01/20008:00:004/01/2000
53/01/20008:27:055/01/2000
63/01/20009:00:08
74/01/20008:27:05
85/01/20008:25:00
95/01/20008:27:10
10
Prices
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F21. / Formula is =ISNA(MATCH(E2+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))Abc
F31. / Formula is =ISNA(MATCH(E3+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))Abc
F41. / Formula is =ISNA(MATCH(E4+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))Abc
F51. / Formula is =ISNA(MATCH(E5+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))Abc
 

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
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
ABCDEF
1DateTimePriceReq DateReq Price
21/2/20008:00:2099.31/2/2000
31/2/20008:25:0078.451/3/2000
41/2/20008:33:2189.21/5/2000
51/2/20009:42:0099.09
61/2/200010:00:0090
71/3/20008:00:0043.89
81/3/20008:14:0056.78
91/3/20008:25:0054.54
101/3/200011:00:0077.89
111/5/20008:10:0094
121/5/20009:10:0099.99
Sheet1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1DateTimePriceReq DateReq Price
22/01/20008:00:2012/01/20002
32/01/20008:25:0023/01/20004
43/01/20008:00:0034/01/20006
53/01/20008:27:0545/01/20007
63/01/20009:00:085
74/01/20008:27:056
85/01/20008:25:007
95/01/20008:27:108
10
Prices
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F21. / Formula is =ISNA(MATCH(E2+TIMEVALUE("8:25"),A$2:A$10+B$2:B$10,0))Abc
 

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 11, 2007
Messages
117

ADVERTISEMENT

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
ABCDEF
1DateTimePriceReq DateReq Price
22/1/20008:00:204352/1/20003
32/1/20008:25:00543/1/20006
43/1/20008:00:0034/1/20006
53/1/20008:27:055655/1/20008
63/1/20009:00:08353
74/1/20008:27:056
85/1/20008:25:00765
95/1/20008:27:108
10
Sheet1
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

Board Regular
Joined
Jul 11, 2007
Messages
117
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

Board Regular
Joined
Jul 11, 2007
Messages
117
Oh my bad! Domenic, your formula worked. Thank you both for your help! I really appreciate it.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,228
Messages
5,594,931
Members
413,953
Latest member
Arthur1471

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