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.




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

 

[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)))