Convert value if the date listed within the column is within the month going across the rows

[Potbellyyemi]

Hello,

In an empty column, I want to be able to multiply the values in column G with the fx rates shown in rows T3 - AD3 if the currency in column F says USD and the invoice date in column D3 is within the months stated across rows T2 - AD3. If the currency in column F says EURO, then the value should bring back the EURO amount in column F rather than converting it.

I basically want to convert the invoice amount to Euro if it is in USD, by the month end rate displayed across rows T - AD, but the rate to use is determined by the invoice date, which must be within the months displayed in rows T2 - AD2. I hope that makes sense.

Does anyone have an idea how to do this using a formula?

Many thanks in advance for any help!

Zuora Revenue Schedule Jan 23.xlsx
	D	E	F	G													T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
2	Invoice: Invoice Date	Invoice: Invoice Number	Account: Currency	Invoice: Amount Without Tax													01-Mar-22	01-Apr-22	01-May-22	01-Jun-22	01-Jul-22	01-Aug-22	01-Sep-22	01-Oct-22	01-Nov-22	01-Dec-22	01-Jan-23	Total YTD
3																	1	0.923617	0.9463424	0.95388	0.97897	1.00538	0.980123	1.005359	0.965624	0.9328	0.921982
4	16/03/2022		EUR	19.50													10.05	9.45	-	-	-	-	-	-	-	-	-	19.50
5	01/04/2022		USD	94.50													-	87.28	-	-	-	-	-	-	-	-	-	87.28
6	04/04/2022		USD	94.50													-	78.55	8.94	-	-	-	-	-	-	-	-	87.50
7	11/04/2022		EUR	39.00													-	26.00	13.00	-	-	-	-	-	-	-	-	39.00
8	15/04/2022		USD	39.00													-	19.21	17.22	-	-	-	-	-	-	-	-	36.43
9	15/04/2022		EUR	99.00													-	52.80	46.20	-	-	-	-	-	-	-	-	99.00
10	16/04/2022		EUR	19.50													-	9.75	9.75	-	-	-	-	-	-	-	-	19.50
11	01/05/2022		USD	94.50													-	-	89.43	-	-	-	-	-	-	-	-	89.43
12	04/05/2022		USD	94.50													-	-	80.77	8.73	-	-	-	-	-	-	-	89.50
13	11/05/2022		EUR	39.00													-	-	26.40	12.60	-	-	-	-	-	-	-	39.00
14	15/05/2022		USD	39.00													-	-	20.21	16.83	-	-	-	-	-	-	-	37.04
15	16/05/2022		EUR	19.50													-	-	10.05	9.45	-	-	-	-	-	-	-	19.50
16	01/06/2022		USD	94.50													-	-	-	90.14	-	-	-	-	-	-	-	90.14
17	04/06/2022		USD	94.50													-	-	-	81.13	9.25	-	-	-	-	-	-	90.38
18	11/06/2022		EUR	39.00													-	-	-	26.00	13.00	-	-	-	-	-	-	39.00
19	15/06/2022		USD	39.00													-	-	-	19.84	17.82	-	-	-	-	-	-	37.66
20	16/06/2022		EUR	19.50													-	-	-	9.75	9.75	-	-	-	-	-	-	19.50
21	01/07/2022		USD	94.50													-	-	-	-	92.51	-	-	-	-	-	-	92.51
22	04/07/2022		USD	94.50													-	-	-	-	83.56	9.20	-	-	-	-	-	92.75
23	06/07/2022		EUR	78.40													-	-	-	-	65.77	12.63	-	-	-	-	-	78.40
24	11/07/2022		EUR	39.00													-	-	-	-	26.40	12.60	-	-	-	-	-	39.00
25	15/07/2022		USD	39.00													-	-	-	-	20.91	17.73	-	-	-	-	-	38.65
26	16/07/2022		EUR	19.50													-	-	-	-	10.05	9.45	-	-	-	-	-	19.50
27	01/08/2022		USD	94.50													-	-	-	-	-	95.01	-	-	-	-	-	95.01
28	04/08/2022		USD	94.50													-	-	-	-	-	85.81	8.97	-	-	-	-	94.78
29	06/08/2022		EUR	78.40													-	-	-	-	-	65.77	12.63	-	-	-	-	78.40
30	11/08/2022		EUR	39.00													-	-	-	-	-	26.40	12.60	-	-	-	-	39.00
31	15/08/2022		USD	39.00													-	-	-	-	-	21.47	17.29	-	-	-	-	38.76
32	16/08/2022		EUR	19.50													-	-	-	-	-	10.05	9.45	-	-	-	-	19.50
33	01/09/2022		USD	94.50													-	-	-	-	-	-	92.62	-	-	-	-	92.62
34	04/09/2022		USD	94.50													-	-	-	-	-	-	83.36	9.50	-	-	-	92.86
35	06/09/2022		EUR	78.40													-	-	-	-	-	-	65.35	13.05	-	-	-	78.40
36	11/09/2022		EUR	39.00													-	-	-	-	-	-	26.00	13.00	-	-	-	39.00
37	13/09/2022		USD	94.50													-	-	-	-	-	-	- 92.62	-	-	-	-	- 92.62
38	14/09/2022		EUR	78.40													-	-	-	-	-	-	44.44	33.96	-	-	-	78.40
39	14/09/2022		EUR	31.80													-	-	-	-	-	-	17.95	13.85	-	-	-	31.80
40	15/09/2022		USD	39.00													-	-	-	-	-	-	20.39	18.30	-	-	-	38.68
41	15/09/2022		USD	39.00													-	-	-	-	-	-	20.39	18.30	-	-	-	38.68
42	16/09/2022		EUR	19.50													-	-	-	-	-	-	9.75	9.75	-	-	-	19.50
43	16/09/2022		EUR	39.00													-	-	-	-	-	-	19.50	19.50	-	-	-	39.00
44	16/09/2022		USD	462.40													-	-	-	-	-	-	226.60	232.44	-	-	-	459.04
45	23/09/2022		EUR	39.00													-	-	-	-	-	-	10.40	28.60	-	-	-	39.00
46	27/09/2022		EUR	752.64													-	-	-	-	-	-	8.28	64.42	62.40	64.27	64.17	263.54
Sheet1

Cell Formulas
Range	Formula
T4:AD46	T4	=IF($F4="EUR",H4,H4*T$3)
AE4:AE46	AE4	=SUM(T4:AD4)

 

[Fluff]

How about

=G4*(IF(F4="EUR",1,SUMIFS($T$3:$AD$3,$T$2:$AD$2,">"&EOMONTH(D4,-1),$T$2:$AD$2,"<="&EOMONTH(D4,0))))

 

[Potbellyyemi]

That worked wonderfully! Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.