Possible INDEX/MATCH FORMULA to get FX Rate in Currency table

[almugs]

Hi,

Hopefully some can help with this.

I have 2 worksheets. The Data sheet includes commission data in various currencies that needs to be translated to British Pounds (GBP). The Currencies tab includes a currency table that includes FX rates for each month since 2013. The process requires us to check contract date in the Data sheet and match it against the PREVIOUS month's FX rate. The FX rate is then used to convert the currency into GBP.

Is there a way of automating this process with something like INDEX and MATCH to automatically fetch the correct FX rate from the currency table and feeding it into the Data Sheet?

FYI - the currency table grows each month by adding new months FX rates. In addition new currencies are added when needed.

I have included a sample of the work book below.

almugs

[FDibbins]

To start with, change the dates in Currencies to real dates. May 15 already is a real date (5/1/2015). make the others 4/1/2015...3/1/2015 etc)

then see if this is what you wanted...
E2=INDEX(Currencies!$A:$BB,MATCH(DATA!$C2,Currencies!$A:$A,0),MATCH(A2,Currencies!$2:$2)-1)
copied down

[almugs]

Brilliant. Thanks very much.

I have however noticed an issue that has cropped due to a couple of instances of my data eg. at 'Data'!A154 and 'Data'!A156. A154 shows a contract start date in July although there is no FX rates for June. The formula has matched to the FX rates for April. According to the definition of how the formula should work this is technically correct but I think it would be better in cases where there are no FX rates for the month previous to the contract start date for an error to be returned (e.g. "No Rate Found")

Is this possible to do?

Again thanks very much for the help with this.

Regards
almugs

[FDibbins]

Test this please, it looks like it is doing what you want, but you would knoe better than me

=IF(MONTH(A2)-MONTH(INDEX(Currencies!$2:$2,MATCH(A2,Currencies!$2:$2)))<=1,INDEX(Currencies!$A:$BB,MATCH(DATA!$C2,Currencies!$A:$A,0),MATCH(A2,Currencies!$2:$2)-1),"No Rate Found")
copied down

[almugs]

Hi Ford,

This works better. However, I have noticed that the contracts for June are not selecting May's FX Rates (the Previous month). They default to April's rates which is incorrect. I have solved this by adding June's date to the currency table (with no FX Rates as I don't have these yet). This seems to fix the problem for June but obviously creates an issue for July as it now picks May's FX rate instead of returning the "No rate Found" error. I think I can accept this level of operation if fixing it becomes too complicated.

almugs

[FDibbins]

Could you point me to some cell refs where this is happening? They all seem to be working OK from what I can see

[almugs]

You can check the following cells in the Data Sheet: A159, A165 and A383. I have also attached a filtered spreadsheet showing dates for June in case that might be more useful.Test06072015.xlsx

[almugs]

Hi Ford,

I dont think you need to worry about this any longer. I have devised a workaround where I have added extra columns with zeros for the months without FX rates. This solves the issue.

Thanks for all your help with this. I will mark this as solved unless you have come up with something more brilliant .

Alpha

[FDibbins]

OK, I think I have it now. Try this...
=IF(MONTH(A2)-MONTH(INDEX(Currencies!$2:$2,MATCH(A2,Currencies!$2:$2)))<=1,INDEX(Currencies!$A:$BB,MATCH(DATA!$C2,Currencies!$A:$A,0),MATCH(A2,Currencies!$2:$2)-IF(MONTH(A2)-MONTH(INDEX(Currencies!$2:$2,MATCH(A2,Currencies!$2:$2)))=1,0,1)),"No Rate Found")