Lookup of multiple cells and deriving the multiple values

Nishh

New Member
Joined
Oct 28, 2015
Messages
1
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.

CurrencyOriginal PrmUSD AmountSterling Prm
GBP51424.37
USD-20000
EUR1219.24
IND5803.57
RUB-670
BSD-24700
BWP11000
CAD342.5
CDF10510
CHF4500
CLF229500
CLP9000
CNH11000
CNY803.19
COP3750
GBP900
USD-235261.13
EUR19035
IND22800
RUB1037531.25
GBP-556.03

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0

Forum statistics

Threads
1,215,261
Messages
6,123,942
Members
449,134
Latest member
NickWBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top