SumIfs (I think)

[ali_jellybean]

Hello,

I think I've overcomplicated what I'm trying to do and confused myself so any help would be very welcome.

The organisation runs many projects in various currencies and we sometimes get issues with the project team using the wrong FX rate or using the right rate but the wrong way around. I'm trying to make it simpler for them by having the FX rate auto-populate into the cost logs of the projects.

I have the FX table:

And the project cost log;

And what I want is to have the formula look for the date, transaction currency and grant currency and if it finds them then return the FX rate into the Exchange Rate cell in the cost log to be used in the calculation of the 'Total in Grant Currency'

Is this possible, do I need to change things, or do I need a different way of working this out?

Thanks so much for any guidance

 

[FormR]

Hi, here's one way you can try (you'll need to adjust to your actual layout).

Book1
	A	B	C	D	E	F	G	H	I	J
1	Transaction	EUR	GBP	EUR	USD		Date	Transaction Currency	Grant Curreny	Exchange Rate
2	Grant	GBP	EUR	USD	EUR2		20/02/2024	GBP	EUR	0.49864
3	01/01/2024	0.37630	0.80116	0.62456	0.67828		01/06/2024	EUR	GBP	0.33799
4	01/02/2024	0.78831	0.49864	0.23123	0.37317		30/11/2024	USD	EUR2	0.27260
5	01/03/2024	0.42950	0.77877	0.01203	0.35369
6	01/04/2024	0.79817	0.64051	0.15706	0.24360
7	01/05/2024	0.65501	0.88737	0.34724	0.98882
8	01/06/2024	0.33799	0.08963	0.57887	0.41945
9	01/07/2024	0.90052	0.50448	0.95618	0.61244
10	01/08/2024	0.30969	0.23321	0.65261	0.04405
11	01/09/2024	0.36929	0.35186	0.46035	0.37453
12	01/10/2024	0.91863	0.72126	0.17261	0.69097
13	01/11/2024	0.17794	0.27242	0.37041	0.27260
14	01/12/2024	0.10290	0.62427	0.21375	0.44377
Sheet1

Cell Formulas
Range	Formula
J2:J4	J2	=INDEX($B$3:$E$14,XMATCH(EOMONTH(G2,-1)+1,$A$3:$A$14),XMATCH(H2&"|"&I2,$B$1:$E$1&"|"&$B$2:$E$2))
A4:A14	A4	=EDATE(A3,1)

 

[ali_jellybean]

Amazing, thank you so much