Looking up a value if it matches 2 or more fields

[Peter_Schindler]

Hello,

I don’t seem to understand Airtable.

I would like to have a Table of Transactions as follows:

.

• TransactionID field is Autonumber
• Date, Description, Amount, Currency are entered
• Year is the formula Year(Date)

In addition, I have exchange rates between my business currency (HKD) and other currencies, by year: one set of exchange rate for 2019, one for 2020, in the future for 2021, 2022, etc.

I would like to have a table that contains the exchange rates for each year, for example like this:

Finally, the RateForthisTx Field in the Transaction Table should be the result of a lookup(Currency, Year), e.g., lookup(AUD, 2019), or lookup (CHF, 2020), whatever corresponds to the actual values in a given transaction.

I must be thick, but I can’t find a solution Airtable, unless, perhaps, we’d have to resort to Scripts.

Once again, a big thank you to anyone who can help.

Peter

[Mohamed_Swella1]

Hi @Peter_Schindler,

You can do the following,

1. In the ExchangeRates table, copy the Primary Field into another field. (insert a new field and copy it)

2. Now in the Primary Field, Make a formula that is Currency&", "&Year

3. In the Transactions table, creat a Linked Field that links to the Exchange Rates (you can do this manually or via script, look in the community for scripts for linked records).

4. Now you can do your lookup.

BR,
Mo

[Peter_Schindler]

Hello Mo,

thanks again very much. Will try it!

Cheers,
Peter

[GavinA]

@Peter_Schindler Unless I am mistaken, the app we have built, Currency Converter (Currency Converter - Apps - Airtable Marketplace) may be a help if this is still a problem. We are constantly looking for ways we may improve it so let us know if there is something missing :slightly_smiling_face: