Formula to return value based on start date and end date?

[reacon84]

Hi all, I'm hoping someone can help me with a spreadsheet I'm trying to build. The top table is where I've got to so far - I have a Start Date, End Date and a Payment amount. I've used =EOM(cell, -1)+1 to convert the Start/End Date into the first day of that month, to correspond with the Jan-20 to Dec-20 headers.

What I'd like to do is have a formula to return the payment amount in the correct cell if it falls within that range (ie the output table at the bottom).

Thanks in advance.

Start Date	Start Date Month	End Date	End Date Month	Payment	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
08/10/2020	01/10/2020	08/01/2022	01/01/2022	427
03/10/2018	01/10/2018	03/10/2021	01/10/2021	379
29/05/2019	01/05/2019	29/05/2022	01/05/2022	800
09/09/2019	01/09/2019	09/03/2022	01/03/2022	560
04/10/2018	01/10/2018	04/04/2020	01/04/2020	646
15/05/2019	01/05/2019	15/10/2020	01/10/2020	262
11/06/2019	01/06/2019	11/06/2021	01/06/2021	573
20/01/2020	01/01/2020	18/08/2021	01/08/2021	292
16/04/2019	01/04/2019	23/04/2020	01/04/2020	542
08/10/2020	01/10/2020	08/01/2022	01/01/2022	542
09/11/2020	01/11/2020	09/08/2022	01/08/2022	427
Start Date	Start Date Month	End Date	End Date Month	Payment	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
08/10/2020	01/10/2020	08/01/2022	01/01/2022	427										427	427	427
03/10/2018	01/10/2018	03/10/2021	01/10/2021	379	379	379	379	379	379	379	379	379	379	379	379	379
29/05/2019	01/05/2019	29/05/2022	01/05/2022	800	800	800	800	800	800	800	800	800	800	800	800	800
09/09/2019	01/09/2019	09/03/2022	01/03/2022	560	560	560	560	560	560	560	560	560	560	560	560	560
04/10/2018	01/10/2018	04/04/2020	01/04/2020	646	646	646	646	646
15/05/2019	01/05/2019	15/10/2020	01/10/2020	262	262	262	262	262	262	262	262	262	262	262
11/06/2019	01/06/2019	11/06/2021	01/06/2021	573	573	573	573	573	573	573	573	573	573	573	573	573
20/01/2020	01/01/2020	18/08/2021	01/08/2021	292	292	292	292	292	292	292	292	292	292	292	292	292
16/04/2019	01/04/2019	23/04/2020	01/04/2020	542	542	542	542	542
08/10/2020	01/10/2020	08/01/2022	01/01/2022	542										542	542	542
09/11/2020	01/11/2020	09/08/2022	01/08/2022	427											427	427

 

[steve the fish]

Maybe this in F2 copied across and down:

=IF(AND($B2<=F$1,$D2>=F$1),$E2,"")

 

[reacon84]

Ah excellent! Thank you!

I always think it's going to be more complex than it is - I need to keep it simple!

Thanks again.

 

[JackDanIce]

Try:

Other.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Start Date	Start Date Month	End Date	End Date Month	Payment	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
2	08/10/2020	01/10/2020	08/01/2022	01/01/2022	427	0	0	0	0	0	0	0	0	0	427	427	427
3	03/10/2018	01/10/2018	03/10/2021	01/10/2021	379	379	379	379	379	379	379	379	379	379	379	379	379
4	29/05/2019	01/05/2019	29/05/2022	01/05/2022	800	800	800	800	800	800	800	800	800	800	800	800	800
5	09/09/2019	01/09/2019	09/03/2022	01/03/2022	560	560	560	560	560	560	560	560	560	560	560	560	560
6	04/10/2018	01/10/2018	04/04/2020	01/04/2020	646	646	646	646	646	0	0	0	0	0	0	0	0
7	15/05/2019	01/05/2019	15/10/2020	01/10/2020	262	262	262	262	262	262	262	262	262	262	262	0	0
8	11/06/2019	01/06/2019	11/06/2021	01/06/2021	573	573	573	573	573	573	573	573	573	573	573	573	573
9	20/01/2020	01/01/2020	18/08/2021	01/08/2021	292	292	292	292	292	292	292	292	292	292	292	292	292
10	16/04/2019	01/04/2019	23/04/2020	01/04/2020	542	542	542	542	542	0	0	0	0	0	0	0	0
11	08/10/2020	01/10/2020	08/01/2022	01/01/2022	542	0	0	0	0	0	0	0	0	0	542	542	542
12	09/11/2020	01/11/2020	09/08/2022	01/08/2022	427	0	0	0	0	0	0	0	0	0	0	427	427
Data

Cell Formulas
Range	Formula
F2:Q12	F2	=$E2*(F$1=MEDIAN($B2,$D2,F$1))