# How to convert currencies in Excel based on historical conversion rates?

I want to make a small program in Excel that allows the user to convert certain currencies (USD, Euro, AED, CHF and GBP) to Kuwaiti dinar. The user can set the amount, the currency and the month, and the program gives the answer in Kuwaiti dinars.

I have the exchange rates in a separate Excel file - each month in a spreadsheet, and each spreadsheet has two columns, currency and rate.

I have to multiply the given amount of the given currency by the rate of that currency on the given month. How can I do that?

-

## 1 Answer

You need to investigate `VLookup` and `Indirect`. `VLookup` allows you to query a cell for a value (in your case the input Currency type), and look up a table to find that value and return the value in the same row in a different column in that table (in your case the exchange rate).

`Indirect` allows you to use a string as a reference to a worksheet location. So, if the content of cell A1 = 'X256', you can use the formula `=10*Indirect(A1)` and the result will be whatever is in cell X256 multiplied by 10

Assuming the sheet your user fills in has looks a bit like this

``````            A          |      B      |          C           |         D
1     ConvertAmount    |   Month     |   ExchangeCurrency   |   ExchangeAmount
2          25.00       |    Jan      |        GBP           |
``````

and the rest of the sheets are named Jan, Feb, Mar, Apr etc and have the Currency type in alpha order and Currency and Rate in columns A and B respectively of each sheet.

You can use a formula like:
`=VLOOKUP(C2, INDIRECT(CONCATENATE(B2, "!A1:B5")),2, FALSE)*A2`
in cell D2 to get the result you require. Note that you will want to set this range to match the number of currencies you are converting from.

You might want to make sure that the user can only put in values that match your setup, perhaps using validation. For example, if the user types January instead of Jan, the sheet referencing trick won't work. Also, if the user types 'Pounds Sterling' instead of GBP, the VLookup in the other sheet won't work.

-
the sheet named by months, and there is 2 columns the currency and its rate. i didnt get ur answer very well :"If the month name is the same as the sheet name for that month, then you can just supply the given month name as the part of the table reference of where you want to retrieve the data" please i need mo explanation thank u :) – Maroom Jan 5 '13 at 11:18
@Maroom answer updated. – mcalex Jan 7 '13 at 10:25