Find correct data based on date ranges

[Shad99]

Hi All, I have been trying to work this out myself but I really do need help

Below is a table I made to work out the kg/DM(Dry matter in the grass) by just inserting the CM and the Date measured.
Have tried index formular but get #N/A error =INDEX($F$2:$O$6,MATCH(1,INDEX(($F$1:$T$1=A2)*(($D$2:$D$6<=B2)*(($E$2:$E$6>=B2)+($E$2:$E$6=""))),0,1),0))

CM Measured

Measured Date

KG/DM

Season Start Dates

Season End Date

15​

14​

13​

12​

11​

10​

9​

8​

7​

6​

9​

24/01/2022​

#N/A​

23/09/2022​

15/11/2022​

3250​

3130​

3010​

2880​

2750​

2620​

2470​

2320​

2150​

1980​

10​

9/02/2022​

15/11/2022​

22/12/2022​

3930​

3740​

3550​

3350​

3150​

2960​

2750​

2540​

2330​

2110​

22/12/2022​

21/03/2022​

4610​

4340​

4080​

3820​

3560​

3290​

3030​

2770​

2500​

2240​

21/03/2022​

21/06/2022​

4210​

3970​

3720​

3480​

3240​

3000​

2750​

2510​

2270​

2020​

21/06/2022​

23/09/2022​

3050​

2890​

2730​

2580​

2420​

2260​

2100​

1940​

1780​

1620​

 

[Toadstool]

Hi Shad99,

I think you mixed rows and columns together. INDEX needs a row then a column so for row I'm doing the dates <= and >= check and for column I'm looking for the CM in row 1.

I also note your season dates overlap by 1 day, some dates were 2022 but probably should be 2023 so I've made some changes.

Shad99.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	CM Measured	Measured Date	KG/DM	Season Start Dates	Season End Date	15	14	13	12	11	10	9	8	7	6
2	9	24-Dec-22	3030	23-Sep-22	15-Nov-22	3250	3130	3010	2880	2750	2620	2470	2320	2150	1980
3	10	09-Feb-23	3290	16-Nov-22	22-Dec-22	3930	3740	3550	3350	3150	2960	2750	2540	2330	2110
4	6	22-Sep-23	1620	23-Dec-22	21-Mar-23	4610	4340	4080	3820	3560	3290	3030	2770	2500	2240
5	15	23-Sep-22	3250	22-Mar-23	21-Jun-23	4210	3970	3720	3480	3240	3000	2750	2510	2270	2020
6	11	22-Mar-23	3240	22-Jun-23	23-Sep-23	3050	2890	2730	2580	2420	2260	2100	1940	1780	1620
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX($F$2:$O$6,MATCH(1,INDEX(($D$2:$D$6<=B2)*($E$2:$E$6>=B2),0,1),0),MATCH(A2,$F$1:$O$1,0))

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	CM Measured	Measured Date	KG/DM	Season Start Dates	Season End Date	15	14	13	12	11	10	9	8	7	6
2	9	24/01/2022	3030	23/09/2021	15/11/2021	3250	3130	3010	2880	2750	2620	2470	2320	2150	1980
3	10	02/09/2022	2260	15/11/2021	22/12/2021	3930	3740	3550	3350	3150	2960	2750	2540	2330	2110
4				22/12/2021	21/03/2022	4610	4340	4080	3820	3560	3290	3030	2770	2500	2240
5				21/03/2022	21/06/2022	4210	3970	3720	3480	3240	3000	2750	2510	2270	2020
6				21/06/2022	23/09/2022	3050	2890	2730	2580	2420	2260	2100	1940	1780	1620
Primary

Cell Formulas
Range	Formula
C2:C3	C2	=FILTER(FILTER($F$2:$O$6,($D$2:$D$6<=B2)*($E$2:$E$6>=B2)),$F$1:$O$1=A2)