At times, when dealing with currency, we need to convert currency in Excel very frequently. It is quicker and handier if we can use any formula to convert currency. In this article, I will show you 4 formula examples to convert currency in Excel.
Here, in our dataset, the USD currency amount is there. And, we have to convert these amounts into Euro(EUR), British pound sterling(GBP), Indian rupee(INR), Canadian dollar(CAD), and Japanese yen(JPY) currencies accordingly. Here, I will show you 4 practical examples with 4 formulas to convert currencies in Excel.
At first, make a dataset for the currencies and exchange rates in respect of USD.
Now, explore the formulas one by one.
1. Converting Currency with a Simple Excel Formula
You can convert currencies manually by extracting the exchange rates from the internet and multiplying the rates individually with the given amount to get the amount in your desired currency.
Follow the steps below to do so. 👇
Steps:
- First, click on cell D5. Put an equal sign(=) in the cell.
- Subsequently, reference the cell of the given USD amount and multiply it with the desired currency exchange rate cell reference.
For example, apply the following formula.
=B5*G5
- Now, as the currency serial in our dataset and the currency serial in our required calculation is the same, the formula would be the same through every cell below. So, put your cursor on the right bottom place of your first calculated cell. Subsequently, drag the fill handle below to copy the formula to all the cells below.
Thus, you have converted your given USD amounts properly. The result will look like this. 👇
Read More: How to Automate Currency Conversion in Excel (5 Easy Methods)
2. Using Nested IF Formula to Convert Currencies
You can also use nested IF rather than referencing exchange rates cell reference every time.
Now, go through the steps below to convert currency using nested IFs. 👇
Steps:
- First of all, click on cell D5.
- Write the following formula now.
=IF(C5="USD",1,IF(C5="EUR",0.94,IF(C5="GBP",0.8,IF(C5="INR",77.61,IF(C5="CAD",1.28,IF(C5="JPY",127.77))))))*B5
- Now, drag the fill handle below and thus the formula will be copied to all the cells below.
Thus, your given USD amounts are converted to the desired currency amount. The result will look like this. 👇
🔎 Breakdown of the Formula:
- =IF(C5=”USD”,1
Writing =IF enables the IF function of Excel. The second argument requires the input of the value returned when the logical test is true. If our desired currency is USD, we have set our exchange rate as 1. So, in the 2nd argument, we write 1. - =IF(C5=”USD”,1,IF(C5=”EUR”,0.94
Now, if the first logical test returns false, it will go to the second IF, and at the first argument, it will test if the desired currency is EUR. If the currency is EUR, true will be returned. Now, in the second argument, we have to set the value for the true return of the logical test. As the EUR exchan`ge rate is 0.94 in respect of the USD, so we write 0.94 in the second argument. - =IF(C5=”USD”,1,IF(C5=”EUR”,0.94,IF(C5=”GBP”,0.8,IF(C5=”INR”,77.61,IF(C5=”CAD”,1.28,IF(C5=”JPY”,127.77))))))*B5
After getting the actual exchange rate of currency using IFs, multiply it with the given USD amount to get the desired currency amount. Remember, the Asterisk sign (*) is used for multiplication in Excel.
Read More: How to Convert USD to CAD in Excel (4 Quick Tricks)
Similar Readings
- Convert Euro to USD in Excel (2 Handy Methods)
- How to Get Exchange Rate by Date in Excel (2 Suitable Methods)
- How to Convert CAD to USD in Excel (4 Easy Ways)
3. Inserting Excel VLOOKUP Function to Convert Currency
You can convert currency using the VLOOKUP formula too.
Vertical Lookup is spelled VLOOKUP. It is mostly used to extract a value from table data or a predetermined range of data.
Now, follow the steps below to use this function to convert currency. 👇
Steps:
- Then write the following formula now in cell D5.
=VLOOKUP(C5,$F$5:$G$10,2,FALSE)*B5
- And drag the fill handle all the way.
Thus, your given USD amounts are converted to the desired currency amount. The result will look like this. 👇
Read More: How to Convert USD to Euro in Excel (3 Useful Methods)
4. Using INDEX-MATCH Formula for Converting Currency in Excel
You can also use the INDEX & MATCH functions to convert currency in Excel.
The INDEX function enables the extraction of a value both horizontally and vertically. It has mainly 3 arguments. Such as: array, row_num and column_num(optional).
The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.
Now, use the following steps to use these functions to convert currency. 👇
Steps:
- Enter the following formula in cell D5.
=INDEX($F$4:$G$10,MATCH(C5,$F$4:$F$10,0),2)*B5
- Now, put your cursor on the right bottom corner of the cell. Subsequently, drag the fill handle below all the way.
Subsequently, your given USD amounts are converted to the desired currency amount. The result will look like this. 👇
- MATCH(C5,$F$4:$F$10,0)
We reference the C5 cell as the first argument as this is the cell carrying desired currency and this is the value that we have to look for in the dataset.
Now, the MATCH function only gives the index number of the value. So, we have to select the cells where the currencies locate. That’s why we have chosen the F4:F10 cell reference. Remember, you must use an absolute array here. So, you need to absolutely refer with a dollar($) sign or press F4.
And, the last argument denotes the type of match. For an Exact match, we have written 0 here. - =INDEX($F$4:$G$10,MATCH(C5,$F$4:$F$10,0),2)
The first argument is the array F4:G10 from which we would extract the values. Remember, this array should also be absolute.
In the second argument, we have used the MATCH function, and so the MATCH function will return the desired row index number according to the currency automatically.
The third argument requires the column index number of the array for finding the desired cell value. As the exchange rate is situated on the second of our selected array, we write 2 in the third argument’s place.
Read More: How to Create Real Time Currency Converter In Excel
Things to Remember
- When using the VLOOKUP function, remember that this is a vertical lookup process. So, you can only look up your values through the columns. You can not look up your value through the horizontal rows.
- Another thing, you should remember is that, when selecting the table array, keep the lookup value column as the first column in your selection. And, the return value column index number will be put according to this serial.
- If you lookup numbered values, the range_lookup argument is not so important. But, if you lookup text values, it is very advisable to put the range_lookup argument as FALSE, if you want an exact match always.
- The INDEX function has a great advantage over the VLOOKUP function. Using the INDEX function, you can extract values both vertically and horizontally.
- The MATCH function doesn’t return a cell value. It returns the index number of the lookup value.
Download Practice Workbook
You can download and practice from our workbook here.
Conclusion
So, here I have shown you the quickest and most suitable formula examples to convert currency in Excel. You can follow any of these ways to accomplish your targets in this regard. I hope you find this article a great help. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this.
