# Conditional Lookup?

#### uberathlete

##### Board Regular
Hi everyone, I really need some help. 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 the last price of the previous date 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 last price of the previous date which is 77.89 and must be highlighted as well.

The result should then be like this:

Anyone know how I could do this? Any suggestions would be greatly appreciated. Thank you!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Domenic

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

Hope this helps!

#### uberathlete

##### Board Regular
Thanks, I'll try that.

Replies
2
Views
1K
Replies
1
Views
885
L
Replies
1
Views
462
Replies
2
Views
273
Replies
4
Views
243

1,190,581
Messages
5,981,767
Members
439,734
Latest member
hmopheim

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