date check with exchange rate

[TJ]

Hi,

I have below information

Cell A=dates
Cell B =Amount
Cell C= CurrencyYEN or USD
Cell D= CDN equivalanet ( formula)

Pick data
Cell G=dates
Cell H=USD
Cell I=Ye
=ROUND(IF($G$5<=A7,$H$5*B7,IF($G$4<=A7,$H$4*B7,IF($G$3<=A7,$H$3*B7,IF($G$2<=A7,$H$2*B7)))),2)
I want the formula to check for what type of currency (USD or YEN) it is &
then automatically pick up the exchange rate
Plus if I go below the date range it works but If I pick up future date it
gives me the last exchange rate
I hope I have listed all the information, I can email sheet if required

thank you for your time & assistance
TJ