Explanation
The formula in this example converts amounts in USD to other currencies using currency codes. Available currencies and exact conversion rates can be adjusted by editing the values in the table on the right.
The core of this formula is the VLOOKUP function, configured like this:
=VLOOKUP(D5,xtable,2,0)
Here, the lookup value comes from column D, table array is the named range "xtable", column index is 2, and match type is set to exact match.
In this configuration, VLOOKUP finds the currency in the table, and retrieves the conversion rate from column H. If the currency code
Nested IF equivalent
With a formula based on nested IFs, the solution looks like this:
=IF(D5="usd",1,
IF(D5="eur",0.84,
IF(D5="yen",112.35,
IF(D5="can",1.23,
IF(D5="gpb",0.74,
IF(D5="cny",6.59))))))*B5
Line breaks added for better readability.
Note: VLOOKUP is generally a better solution, since values are easy to see and edit at any time, the formula is easier to read, and exchange rate values are not stored redundantly in many different formulas.
INDEX and MATCH equivalent
With the INDEX and MATCH funcions, the solution looks like this:
=INDEX($H$5:$H$10,MATCH(D5,$G$5:$G$10,0))*B5
