Forum Discussion

adminragnar's avatar
adminragnar
Copper Contributor
Sep 14, 2024
Solved

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 autom...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Sep 14, 2024

    adminragnar 

    =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's avatar
OliverScheurich
Gold Contributor
Sep 14, 2024

adminragnar 

=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.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 14, 2024

      adminragnar 

      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.

Resources