Need to convert value depending on currency and date

[Ascensus]

I need a formula that will identify what exchange rate to use to convert E2 in to F2. The parameters are a date the B2 relates to and the currency in A2. The currencies are CHF, EUR & USD.
The first table is the data and I need to work out the value in the F1. The second table is a extract from the currency table that I want to look up to. There is a table for each currency.

I have tried a vlookup with multiple criteria but haven't had much success.

	A	B	C	D	E	F
1	Currency	Year	Purchase Date	Product Code	Curr Price	GBP Price
2	GBP	2000	01/05/2000	CH0004	37.00
3	CHF	2016	29/03/2016	CH0005	3.05
4	USD	2024	04/08/2024	CH0006	72.00
5	EUR	2023	06/09/2023	CH0007	1.50
6	GBP	2009	01/12/2009	CH0008	2.46

	A	B	C
1	Curr.	Date	Rate
2	USD	2008	0.5445730
3	USD	2009	0.6411690
4	USD	2010	0.6474910
5	USD	2011	0.6236290

 

[alansidman]

This can be achieved, but it will be necessary for your sample data to be complete for all currency conversions and years for us to provide you with a solution.

 

[deLockloire]

I'm not sure I understand it correctly. However, if I do, what I would do is convert the currency tables to excel Tables and name them as their currency code (e.g., GBP, USD, EUR, CHF). You can also drop Column A then for each Currency, as there is no need for them any longer.

Then just do this in column F (assuming your main data table is also a Named Table):

=XLOOKUP([@Year], INDIRECT([@Currency]&"[Date]"), INDIRECT([@Currency]&"[Rate]") , , -1) * [@[Curr Price]]