Embedded Vlookup for values within Date Range --- Help!

[Mgess]

Hello! Please comment if you have an idea about how to do this!

I am trying to convert foreign sales prices dating back 10 years into USD amounts using a vlookup function embedded within an If function (I also tried a Lookup function) to return the appropriate conversion rate from the appropriate year. The one I am using keeps returning incorrect values. I have approximately 10,000 observations, so i really want to automate this.

	A	B	C	D	E	F	G	H	I
1	Date	Sales Price	Currency	Currency Conversion	Price (USD)		From	To	USD/HKD
2	4/6/15	106,250	HKD	???	D2*106,250		9/1/14	8/31/15	7.75
3	4/6/15	62,500	HKD	???	D3*62,500		9/1/13	8/31/14	7.73
4	7/16/14	40,000	GBP	???	D4*40,000
5							From	To	USD/GBP
6							9/1/14	8/31/15	.649
7							9/1/13	8/31/14	.603

<tbody>
</tbody>

 

[BobUmlas]

it would help if you posted the formula you're using, and in which cell(s).

 

[Mgess]

I was using the following formula (the formula on my actual spreadsheet refers to different cells. This formula refers to my example above):

=If(C2="USD",1,If(C2="HKD",Lookup(A2,G2:H3,I2:I3),If(C2="GBP",Lookup(A2,G6:H7,I6:I7),0)))