Need help for a correct formula...

Ali Haider

Hi, I need to fetch the data from monthly operating rate of currency in which i have diffrent currencies. i have some monthly fixed exchnage rates agnist the USD. so i need a perfect formula to get the exact currency exchange rate for the specific month according to month + year for my main sheet from another forex exchnage sheet with have the forex exchnage rate for a couple of years.

I am using below formula and its working okay for this month but when i will add new rates for next month than it will create a problem for me to diffrenciate the exchnage rate of next month. Can we bind this formulla with any date and month ?

=[PO Amount Amount without VAT/Sale Tax]@row * INDEX({Monthly Operating Rates Range 13}, MATCH(Currency@row, {Monthly Operating Rates Range 8}, 0))

TVang

Hi, @Ali Haider,

Assuming that subsequent month rates are appended to the existing ones (like in the image below) then one approach is to use COLLECT() and JOIN() to return the appropriate rate. COLLECT() allows you to use multiple criteria to help identify the exact rate. 

COLLECT( {MOR Rates}, {MOR Month} , >TODAY() , {MOR Code}, Currency@row) returns the rates that match the currency-code and the date in Month (that is greater than today).

...then use JOIN()...

JOIN( COLLECT( {MOR Rates}, {MOR Month} , >TODAY() , {MOR Code}, Currency@row), "")

JOIN() changes the rate into a text string so you'll need to use VALUE() to change it back into a numeric value.

=VALUE(JOIN(COLLECT({MOR Rates}, {MOR Month}, >TODAY(), {MOR Code}, Currency@row), "")) * [PO Amount Amount (without VAT/Sale Tax)]@row

If you want a column for September, one for October, one for November, and so forth, you'll need to modify the criterion for {MOR Month}. For example, for the Sep 2022 column, the criterion would be, AND(@cell>=DATE(2022,9,1), @cell<=DATE(2022,9,30)) . So for October, instead of using >TODAY(),the formula would look like,

=VALUE(JOIN(COLLECT({MOR Rates}, {MOR Month}, AND(@cell>=DATE(2022,10,1), @cell<=DATE(2022,10,31)), {MOR Code}, Currency@row), "")) * [PO Amount Amount (without VAT/Sale Tax)]@row 

Cheers!