Forum Discussion
Excel formula for adding correct exchange rate to correct cell
Hello,
May I ask for help with this please.
I am running a web store, and in columns A-C (marked as 1) you can see parts of the sales report (see screenshot).
In column D, I wish to automatically insert the correct currency exchange rate for the Purchase Date (column B) and for the currency that was used to pay the order (column C).
Columns F-H and J-L (marked as 2 and 3) contains the exchange rates that should be used for Euro and USD.
So to get the correct currency exchange rates into the different rows of column D, the formula needs to:
-check that the order date (column B) is the same as the date in column F or J.
-check that the order currency (column C) is the same as the currency in column G or K.
For example:
-In cell D2, the correct currency exchange rate to be inserted is 10,35498 (from cell L10), because the order date (column B) is 2024-09-11 and the currency (column C) is USD.
Thank you!
=IF(C2="USD",INDEX($L$2:$L$16,MATCH(B2,$J$2:$J$16,0)),INDEX($H$2:$H$16,MATCH(B2,$F$2:$F$16,0)))
You can use IF along with INDEX and MATCH.
- OliverScheurichGold Contributor
=IF(C2="USD",INDEX($L$2:$L$16,MATCH(B2,$J$2:$J$16,0)),INDEX($H$2:$H$16,MATCH(B2,$F$2:$F$16,0)))
You can use IF along with INDEX and MATCH.
- adminragnarCopper Contributor
OliverScheurich Perfect, thank you very much!
I'd only use 1 instead of 0 as third parameter in MATCH for OliverScheurich solution. Exchange rate skips non-trading dates. Look, you have exchange rate for Aug 30 and next for Sep 2. Since Aug 31 and Sep 01 are on weekend.
However, order could be date on last date of the month, i.e. Aug 31. If so with parameter 0 you have an error. With parameter 1 you have exchange rate for the last previous date, i.e. for Aug 30. Which is correct, exchange rates for not-trading dates are the same as for last trading date.
- Patrick2788Silver Contributor
An INDEX variant using the seldom used reference arrangement:
=LET( i, XMATCH(dates, EUR[date]), a, XMATCH(currency, {"EUR", "USD", "SEK"}), INDEX((EUR, USD, SEK), i, 3, a) )
- adminragnarCopper ContributorPatrick2788 Thank you!