Lookup based on current and previous date?

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
Hi everyone, I need some help badly. Basically, here's the situation:

Excel Workbook
ABCDEFGHI
1DateSymbolTimePriceTodaySymbolToday's PricePreviousDay's Price
21/4/00BAXM08:00:30 AM94.091/4/00BAXH0
31/4/00BAXH08:01:00 AM94.511/5/00BAXH0
41/4/00BAXH08:01:00 AM94.511/7/00BAXM0
51/4/00BAXH08:01:06 AM94.51
61/5/00BAXM08:01:12 AM94.09
71/5/00BAXM08:01:42 AM94.09
81/5/00BAXM08:01:48 AM94.09
91/5/00BAXH08:02:00 AM94.51
101/5/00BAXH08:02:06 AM94.51
111/7/00BAXH08:02:24 AM94.09
121/7/00BAXH08:02:54 AM94.09
131/7/00BAXM08:03:24 AM94.09
Sheet1



I'm trying to fill column H and column I. In column H should be the price of the symbol specified in column G on the last hour of the date specified in column F. So it's like the end of day price of that date.

Column I on the other hand should have the price of the symbol specified in column G on the last hour of the previous (or last) date before the date specified in column F that has that symbol's price.

So in this case, for a Today date of 1/4/00, Today's Price should be the price of BAXH0 on 1/4/00 at the last hour which is 8:01:06 AM. So cell H2 will have 94.51. Cell I2 on the other hand won't have any price because there is no date before 1/4/00.

For a Today date of 1/7/00, Today's Price should be the price of BAXM0 on 1/7/00 at 8:03:24 AM. So cell H4 will have a price of 94.09. In cell I4, the price should be from the previous date containing that symbol at the last hour which is on 1/5/00 at 8:01:48 AM. So the price in cell I4 is 94.09.

The result should thus be:

Excel Workbook
ABCDEFGHI
1DateSymbolTimePriceTodaySymbolToday's PricePreviousDay's Price
21/4/00BAXM08:00:30 AM94.091/4/00BAXH094.51
31/4/00BAXH08:01:00 AM94.511/5/00BAXH094.9994.51
41/4/00BAXH08:01:00 AM94.511/7/00BAXM094.0992.25
51/4/00BAXH08:01:06 AM94.51
61/5/00BAXM08:01:12 AM94.09
71/5/00BAXM08:01:42 AM94.09
81/5/00BAXM08:01:48 AM92.25
91/5/00BAXH08:02:00 AM94.58
101/5/00BAXH08:02:06 AM94.99
111/7/00BAXH08:02:24 AM94.09
121/7/00BAXH08:02:54 AM94.09
131/7/00BAXM08:03:24 AM94.09
Sheet1


Anyone know how I could do this? I hope my description isn't too confusing, but any suggestions would be much appreciated.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Hi everyone, I need some help badly. Basically, here's the situation:

...

I'm trying to fill column H and column I. In column H should be the price of the symbol specified in column G on the last hour of the date specified in column F. So it's like the end of day price of that date.

Column I on the other hand should have the price of the symbol specified in column G on the last hour of the previous (or last) date before the date specified in column F that has that symbol's price.

So in this case, for a Today date of 1/4/00, Today's Price should be the price of BAXH0 on 1/4/00 at the last hour which is 8:01:06 AM. So cell H2 will have 94.51. Cell I2 on the other hand won't have any price because there is no date before 1/4/00.

For a Today date of 1/7/00, Today's Price should be the price of BAXM0 on 1/7/00 at 8:03:24 AM. So cell H4 will have a price of 94.09. In cell I4, the price should be from the previous date containing that symbol at the last hour which is on 1/5/00 at 8:01:48 AM. So the price in cell I4 is 94.09.

The result should thus be:

...
Anyone know how I could do this? I hope my description isn't too confusing, but any suggestions would be much appreciated.

Your 2nd exhibit is inconsistent with the 1st. Below refers to the 2nd...
Book1
ABCDEFGHIJ
1DateSymbolTimePriceTodaySymbolToday'sPrevious
2PriceDay's PriceIdx
336529BAXM00.333680694.0936529BAXH094.51 4
41/4/2000BAXH08:01:00 AM94.511/5/2000BAXH094.9994.519
51/4/2000BAXH08:01:00 AM94.511/7/2000BAXM094.0994.9912
61/4/2000BAXH08:01:06 AM94.51
71/5/2000BAXM08:01:12 AM94.09
81/5/2000BAXM08:01:42 AM94.09
91/5/2000BAXM08:01:48 AM92.25
101/5/2000BAXH08:02:00 AM94.58
111/5/2000BAXH08:02:06 AM94.99
121/7/2000BAXH08:02:24 AM94.09
131/7/2000BAXH08:02:54 AM94.09
141/7/2000BAXM08:03:24 AM94.09
Sheet1


H3, copy down:

=IF(N(J3),INDEX($D$3:$D$14,J3),"")

I3:

Control+shift+enter...

=IF(INDEX($A$3:$A$14,J3)>MIN($A$3:$A$14),LOOKUP(9.99999999999999E+307,IF($A$3:$A$14< F3,$D$3:$D$14,"")),"")

then copy down.

J3:

Control+shift+enter...

=MAX(IF($A$3:$A$14=F3,IF($B$3:$B$14=G3,IF($C$3:$C$14=MAX(IF($A$3:$A$14=F3,IF($B$3:$B$14=G3,$C$3:$C$14))),ROW($A$3:$A$14)-ROW($A$3)+1))))

then copy down.
 

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
Hi Aladin. Just wondering if there's more to the formula you specified for I3? :


Control+shift+enter...

=IF(INDEX($A$3:$A$14,J3)>MIN($A$3:$A$14),LOOKUP(9.99999999999999E+307,IF($A$3:$A$14
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Hi Aladin. Just wondering if there's more to the formula you specified for I3? :


Control+shift+enter...

=IF(INDEX($A$3:$A$14,J3)>MIN($A$3:$A$14),LOOKUP(9.99999999999999E+307,IF($A$3:$A$14

Yes... It's cut off at "<"...

=IF(INDEX($A$3:$A$14,J3)>MIN($A$3:$A$14),LOOKUP(9.99999999999999E+307,IF($A$3:$A$14 < F3,$D$3:$D$14,"")),"")
 

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
Yikes. Aladin, sorry but I checked things and the formula sorta misses some things. In your your table, I believe I5 should be 92.25 (which is a BAXM0 price) . The 94.99 is the price of BAXH0 instead of BAXM0. Would you know how I should modify the formula so that 92.25 shows?

My apologies, I wasn't able to notice the error earlier.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Yikes. Aladin, sorry but I checked things and the formula sorta misses some things. In your your table, I believe I5 should be 92.25 (which is a BAXM0 price) . The 94.99 is the price of BAXH0 instead of BAXM0. Would you know how I should modify the formula so that 92.25 shows?

My apologies, I wasn't able to notice the error earlier.

My fault. I neglected to include the Symbol condition...

I3:

Control+shift+enter...

=IF(INDEX($A$3:$A$14,J3)>MIN($A$3:$A$14),LOOKUP(9.99999999999999E+307,IF($A$3:$A$14 < F3,IF($B$3:$B$14=G3,$D$3:$D$14,""))),"")

then copy down.
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,786
Latest member
Deniel

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