deLockloire
Board Regular
- Joined
- Apr 4, 2008
- Messages
- 116
- Office Version
- 365
- Platform
- Windows
Hey,
I have a table named ECB that contains exchange rate listings for a series of dates. Something like this:
Of course, it is filled up with values. In another table (called Transactions), there are dates, values, currencies, etc. of transactions. Something like this:
What I need is a formula for the EXCHANGE RATE column. What it needs to do is to pull in the second to last (i.e., last but one) listing from the previous month of the DATE of the current transaction for the currency indicated in the CURRENCY column exchange rate from the ECB table. As marked with X and Y. For example, for each transaction that takes place in march, the ECB exchange rate of 2024.02.03 should be used, because that is the last but one listing (the last being 2024.02.04). Similarly, for each transaction in February, the 2024.01.29 listing should be used.
Now I do have a solution, but it feels way too complicated and I was just wondering if there is a simpler way of doing it. My solution is this (using it from the Exchange Rate column of the Transactions Table):
Basically with a FILTER function I create an array of dates from the ECB table that corresponds to the previous month, and get the latest date by putting it through a MAX formula, then I subtract 1 from it , so the result will be one day smaller than the latest day, and this is what I put as a search value in an XLOOKUP, using a -1 match mode and then return the corresponding exchange rate by putting the return array together with an INDIRECT that uses the CURRENCY values to indicate the column.
So I might be wrong here, but there surely must be a simpler way of doing this.
Any ideas?
I have a table named ECB that contains exchange rate listings for a series of dates. Something like this:
| DATES | USD | GBP | SEK | TRY | |
| 2024.01.28 | X | ||||
| 2024.01.29 | |||||
| 2024.01.31 | |||||
| 2024.02.01 | |||||
| 2024.02.03 | Y | ||||
| 2024.02.04 | |||||
| 2024.03.01 | |||||
| 2024.03.02 |
Of course, it is filled up with values. In another table (called Transactions), there are dates, values, currencies, etc. of transactions. Something like this:
| DATE | AMOUNT | CURRENCY | EXCHANGE RATE |
| 2024.02.28 | 26,000 | USD | X |
| 2024.03.02 | 19,000 | GBP | Y |
What I need is a formula for the EXCHANGE RATE column. What it needs to do is to pull in the second to last (i.e., last but one) listing from the previous month of the DATE of the current transaction for the currency indicated in the CURRENCY column exchange rate from the ECB table. As marked with X and Y. For example, for each transaction that takes place in march, the ECB exchange rate of 2024.02.03 should be used, because that is the last but one listing (the last being 2024.02.04). Similarly, for each transaction in February, the 2024.01.29 listing should be used.
Now I do have a solution, but it feels way too complicated and I was just wondering if there is a simpler way of doing it. My solution is this (using it from the Exchange Rate column of the Transactions Table):
VBA Code:
=XLOOKUP(MAX(FILTER(ECB[Date],(ECB[Date] >= DATE(YEAR([@Date]),MONTH([@Date])-1,1)) * (ECB[Date] <= EOMONTH(DATE(YEAR([@Date]),MONTH([@Date])-1,1),0))))-1,ECB[Date],INDIRECT("ECB["&[@Currency]&"]"),,-1)Basically with a FILTER function I create an array of dates from the ECB table that corresponds to the previous month, and get the latest date by putting it through a MAX formula, then I subtract 1 from it , so the result will be one day smaller than the latest day, and this is what I put as a search value in an XLOOKUP, using a -1 match mode and then return the corresponding exchange rate by putting the return array together with an INDIRECT that uses the CURRENCY values to indicate the column.
So I might be wrong here, but there surely must be a simpler way of doing this.
Any ideas?
