Lookup of multiple cells and deriving the multiple values

[Nishh]

Hi there,

I am trying to derive the Insurance Premium for policies where premium is in different currencies. This is in sheet1 which has currency symbol and their corresponding premium. I have a Rate of exchange tab (sheet 2) that converts all currencies into either GBP or USD.

I am trying to converts all premium from different currencies into 1. GBP and 2. USD. not sure which formula will help me complete this. any help is greatly appreciated.

[TABLE="width: 381"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Currency[/TD]
[TD]Original Prm[/TD]
[TD]USD Amount[/TD]
[TD]Sterling Prm[/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD="align: right"]51424.37[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD="align: right"]-20000[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EUR[/TD]
[TD="align: right"]1219.24[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IND[/TD]
[TD="align: right"]5803.57[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]RUB[/TD]
[TD="align: right"]-670[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BSD[/TD]
[TD="align: right"]-24700[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BWP[/TD]
[TD="align: right"]11000[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD="align: right"]342.5[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CDF[/TD]
[TD="align: right"]10510[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CHF[/TD]
[TD="align: right"]4500[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CLF[/TD]
[TD="align: right"]229500[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CLP[/TD]
[TD="align: right"]9000[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CNH[/TD]
[TD="align: right"]11000[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CNY[/TD]
[TD="align: right"]803.19[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]COP[/TD]
[TD="align: right"]3750[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD="align: right"]900[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD="align: right"]-235261.13[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EUR[/TD]
[TD="align: right"]19035[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IND[/TD]
[TD="align: right"]22800[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]RUB[/TD]
[TD="align: right"]1037531.25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD="align: right"]-556.03[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

You haven't described what your rate of Exchange layout looks like.
Assuming this Sheet1 and 51424.37 is in B2 and Sheet2 is your list of rates with a layout of currency GBP rate of exchange and USD rate of exchange.

for GBP
in Sheet1!C2
=VLOOKUP(B2,Sheet2!A$1:C$1000,2,0)*B2

for USD
in Sheet1!D2
=VLOOKUP(B2,Sheet2!A$1:C$1000,3,0)*B2

copy down Sheet1