The Looked up Values auto-enter setting can indeed work for this. You just need to set up the details correctly.
Define your relationship like this (substitute your table and field names for mine):
MainTable::CurrencyType = ExchangeRate::CurrencyType AND
MainTable::TransactDate > ExchangeRate::EffectDate
Sort this relationship by EffectDate in descending order.
Now a looked up value will copy from the matching record with the most recent effectivie date less than or equal to the transaction date.