Is it possible to use a calculated formula field to set a field value based on a look up for a certain value?

Using the Currency table as an example of where we want to do the lookup against...

let's say that we have two custom fields on the Opportunity:
- a custom Currency Code field (this is a drop down field and may contain one of the following currency codes: "USD", "EUR", "CAD" in the list).
- a custom Conversion Rate field (this should be populated from the Currency table based on the value of the custom Currency Code on the Opportunity)

Assuming that the custom Currency Code is set to "CAD" on the Opportunity,
is it possible to use a formula to set the Opportunity's custom Conversion Rate field to the Currency -> "conversion_rate" where the Currency -> "iso4217" = "CAD".

Is something like this doable using a calculated formula? Or are we looking at custom code to pull this off?