In this article, we will learn How to get a currency conversion calculator in Excel.
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.
|=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.
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|
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.
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 firstname.lastname@example.org.
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.
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.
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.