Lookup based on current and previous date?

U

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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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.
Click to expand...

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.
 
Upvote 0
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
Click to expand...
 
Upvote 0
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
Click to expand...
Click to expand...

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,"")),"")
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Sumifs Hours Worked Between Two Dates and Times with Multiple Criteria
2 3
Replies
24
Views
622
Fluff
Fluff
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
302
awoohaw
awoohaw
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W
N
  • Question
Need Formula help
Replies
5
Views
351
AhoyNC
A
A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
373
archibell
A

Forum statistics

Threads
1,214,627
Messages
6,120,610
Members
448,973
Latest member
ChristineC

Share this page

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
Back
Top