Formula to Convert Currency in Excel (4 Useful Examples)

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.


Download Practice Workbook

You can download and practice from our workbook here.


4 Simple Examples to Convert Currency with Excel Formula

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.

Currency and Exchange Rate

Now, explore the formulas one by one.


1. Convert 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

Convert Currency Manually Using Cell Referencing

  • 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.

Drag Fill Handle to Copy Currency Conversion

Thus, you have converted your given USD amounts properly. The result will look like this. ๐Ÿ‘‡

Currency Converted in Excel

Read More: How to Convert CAD to USD in Excel (4 Easy Ways)


2. Use Nested IF to Convert Currencies

You can also use nested IF rather than referencing exchange rates cell reference every time.

The IF function takes three arguments in total. The logical test is the first. This argument wishes to return True or False after the logical test is completed. The value returned when the test returns true is the second argument. The value returned when the test returns false is the third argument.

IF Function Syntax

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

Use Nested IF to Convert Currency

  • Now, drag the fill handle below and thus the formula will be copied to all the cells below.

Drag Fill Handle to Copy the Nested IF Formula

Thus, your given USD amounts are converted to the desired currency amount. The result will look like this. ๐Ÿ‘‡

Converted Currency

๐Ÿ”Ž 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 Automate Currency Conversion in Excel (5 Easy Methods)


3. Use 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.

VLOOKUP Function Syntax

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

Use the VLOOKUP Function to Convert Currency

  • And drag the fill handle all the way.

Drag Fill Handle to Copy the VLOOKUP Function

Thus, your given USD amounts are converted to the desired currency amount. The result will look like this. ๐Ÿ‘‡

Converted Currency

Read More: How to Convert USD to Euro in Excel (3 Useful Methods)


4. INDEX-MATCH Formula to Convert 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).

INDEX Function Syntax

The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.

The MATCH Function Syntax

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

Combine INDEX & MATCH to Convert Currency

  • Now, put your cursor on the right bottom corner of the cell. Subsequently, drag the fill handle below all the way.

Drag Fill Handle to Copy INDEX & MATCH

Subsequently, your given USD amounts are converted to the desired currency amount. The result will look like this. ๐Ÿ‘‡

Converted Currency

๐Ÿ”Ž Breakdown of the Formula:
  • 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: Convert Euro to USD in Excel (2 Handy Methods)


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.

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.


Related Article

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo