How to get currency conversions calculator in Excel


In this article, we will learn How to get a currency conversion calculator in Excel.

Scenario :

Generally, we come across situations where we need to find the currency or money conversion calculator. For this we need a conversion table having the amount and currency to convert. To convert an amount of one currency to another currency, we need to multiply it with a factor which is rate of conversion. But you wouldn't believe, you can perform the task with Excel basic function VLOOKUP function. This function finds the multiplying factor or rate from the table.

1 USD dollar = 0.92 Euros

So 10 USD dollars = 9.2 euros (0.92 is rate)

How to solve the problem?

You must be thinking how is this possible, to perform logical operations over table arrays using VLOOKUP function. VLOOKUP function in excel is very useful, It will get you through some difficult tasks in Excel. VLOOKUP function finds values in table array corresponding to required values and returns the result. For this problem, we will be using the below formula.

Generic formula:

=VLOOKUP ( lookup_currency , usd_conversion_table , col , 0 ) * usd_dollar

lookup_currency : usd to look up currency

usd_conversion_table : usd to currency rate table

col : rate field column of table

0 : exact match

usd_dollar : dollar to convert.

Example :

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we have data in the right side and given an amount (USD) to convert into given currency.

Here the table array is G3:H9. In the table we have the conversion of 1 USD (dollars) to given currency with rate. Here the formula extracts the amount (in yen) when 10$ USD is converted.

Use the formula:

=VLOOKUP ( D4 , G3:H9 , 2 , 0 ) * B4

Explanation:

  1. D4, Yen is the lookup value which looks for rate corresponding to Yen.
  2. The function looks for the value in table G3:H9.
  3. The function looks for the value in 2nd column of the table where 1st column must have the lookup value and rate be the 2nd column.
  4. 0 finds the exact match.

Here the range as table is given as array reference and value as cell reference. Press Enter to get the result.

When 10$ USD is converted to yen comes out to be 1079 yens. Now to get the other conversions just add values parallel to the above value and freeze the table array using ($) char. Then use Ctrl + D to copy formula to remaining cells.

As you can see, now we can get any conversion which are in the conversion table. And using just one Excel function, we get the money conversion or currency conversion using the explained formula above. The function returns error, if doesn't find the match in the function. But VLOOKUP is not a case sensitive function, so it can matches with Yen or YEN or yen. Use Exact function with lookup value, to get the formula for case sensitive look_up value.

Here are all the observational notes regarding using the formula. 

Notes :

  1. The rate array in the formula only works with numbers but the loop_up array can be with numbers as well as text values.
  2. Make sure to check the spelling error in look_up value and lookup array.
  3. If the formula returns #VALUE error, check for the used braces must be present in an order in the formula as shown in the examples in the article.
  4. Lookup array must be the first column of the lookup table.
  5. You cannot look towards right of the lookup array. Make sure to check that the rate array be on left side of the lookup array as shown in article
  6. Careful with the freezing table array and cell references.

Hope this article about How to get currency conversion calculator in Excel is explanatory. Find more articles on lookup and match formulas here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com.

 

Related Articles:

How to use the INDEX function in Excel : Return the value with the index position from the array in Excel using INDEX function.

How to find the last row with mixed data in Excel : working with long unmannered numbers, text or blank cell data. Extract the last row with non blank cell using the formula in Excel.

Finding the Last Day of a Given Month : Returns the last day of a given month.

How to Get Last Value In Column : Find the last value in a column or list.

Difference with the last non blank cell : Returns the SUM of values between given dates or period in excel.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work ODD faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.