Forum Discussion
Excel formula for adding correct exchange rate to correct cell
=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.
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.
- SergeiBaklan Thank you!
SergeiBaklan and OliverScheurich
If I wish to add a third currency, my domestic SEK, as shown in the screenshot columns N-P, how would I write the formula?
Thank you!
Okay, on any Excel that could be
=INDEX( INDEX($F$2:$Q$1000, 0, MATCH(C2, $F$2:$Q$2, 0)+1 ), MATCH(B2, INDEX($F$2:$Q$1000, 0, MATCH(C2, $F$2:$Q$2, 0)-1 ), 1 ) )
