Create daily transactions using daily prices table

Hi

Reposted with attachment

I need to recalculate daily transactions using a daily pricing table.

For some days the daily price covers more than one day and I get an error in my vlookup if a price does not exist for the exact day of the transaction.

What I want to do is have my vlookup use the daily price valid for the day of the transaction.

e.g Daily price for 7/1/2014 of 1.11 applies for 12/1/2014 and 13/1/2014.

See attached sample file.

Regards

Have you considered a Sumifs?
Drop this into C2 and drag/copy the formula down the list... It should give you what you need
=SUMIFS(Prices!$C$3:$C$30,Prices!$A$3:$A$30,"<="&$A2,Prices!$B$3:$B$30,">="&$A2)
Obviously extend your ranges if your list is ever longer (Would set it to max length) if it has no limits and the book is not giant you can just use the entire columns... either way this should do it!
=SUMIFS(Prices!$C:$C,Prices!$A:$A,"<="&$A2,Prices!$B:$B,">="&$A2)

-If you think you are done, Start over -

ELeGault

Hi EleGault

Thanks the sumifs formula worked.

By experimenting I also found that the vlookup formula with true instead of false worked.

Appreciate your help

ajob