# Lookup based on current and previous date?

#### uberathlete

##### Board Regular
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.

##### MrExcel MVP
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
Oh u'r right, sorry about that. I'll go try that right now. Thanks Aladin.

#### uberathlete

##### Board Regular
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

##### MrExcel MVP
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,"")),"")

Thanks!

#### uberathlete

##### Board Regular
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.

##### MrExcel MVP
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.

Replies
1
Views
262
Replies
1
Views
249
Replies
6
Views
316
Replies
0
Views
237
Replies
2
Views
139

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.

### Which adblocker are you using?

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

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