Rachael has attended:
Excel Advanced course
Currency conversion in a table
Hi,
I have a table in Excel with a list of costs in different currencies (USD, EUR, GBP) & I want to convert them all to GBP in a 2nd column.
I need to use the below currency conversion rates set by my company, so don't think I can use the currencies option within data tab.
GBP-EUR 1.14
GBP-USD 1.19
Is there a way to do this using the IF or VLOOKUP formula?
Thanks!
RE: Currency conversion in a table
Hi Rachael,
Thank you for the forum question
Create a Conversion Table:
In a new sheet or a separate area of your current sheet, create a table with the conversion rates.
For example, in cells A1:B3:
A | B
--------|------
Currency| Rate
USD | 1.19
EUR | 1.14
GBP | 1
Use VLOOKUP to Get the Conversion Rate:
Assuming your costs are in column A and the currency codes are in column B, you can use VLOOKUP to get the conversion rate.
In column C, use the following formula to get the conversion rate:
=VLOOKUP(B2, $A$1:$B$3, 2, FALSE)
Convert the Costs to GBP:
In column D, use the following formula to convert the costs to GBP:
=A2 / C2
Here's how it looks in practice:
A B C D
Cost Curr Rate GBP
100 USD 1.19 84.03
200 EUR 1.14 175.44
150 GBP 1 150
In this example:
Column A contains the costs.
Column B contains the currency codes.
Column C uses VLOOKUP to find the conversion rate.
Column D converts the costs to GBP.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
