Matrix/INDEX/MATCH lookup

[rdbauer83]

I have a "calendar" of dates on a worksheet. And I want a cell to return information based on the what day is input by finding the date within the "MATRIX". Highlighted cells are what I need the formulas for and the information in the yellow highlighted cells I have just input showing what I want it to return based on the data in B13 and B17.

Book2
	A	B	C	D	E	F	G	H	I
1	Mon	Tue	Wed	Thurs	Fri	Sat	Sun
2	8/22/2022	8/23/2022	8/24/2022	8/25/2022	8/26/2022	8/27/2022	8/28/2022	11	3
3	8/29/2022	8/30/2022	8/31/2022	9/1/2022	9/2/2022	9/3/2022	9/4/2022	11	4
4	9/5/2022	9/6/2022	9/7/2022	9/8/2022	9/9/2022	9/10/2022	9/11/2022	12	1
5	9/12/2022	9/13/2022	9/14/2022	9/15/2022	9/16/2022	9/17/2022	9/18/2022	12	2
6	9/19/2022	9/20/2022	9/21/2022	9/22/2022	9/23/2022	9/24/2022	9/25/2022	12	3
7	9/26/2022	9/27/2022	9/28/2022	9/29/2022	9/30/2022	10/1/2022	10/2/2022	12	4
8	10/3/2022	10/4/2022	10/5/2022	10/6/2022	10/7/2022	10/8/2022	10/9/2022	13	1
9	10/10/2022	10/11/2022	10/12/2022	10/13/2022	10/14/2022	10/15/2022	10/16/2022	13	2
10	10/17/2022	10/18/2022	10/19/2022	10/20/2022	10/21/2022	10/22/2022	10/23/2022	13	3
11	10/24/2022	10/25/2022	10/26/2022	10/27/2022	10/28/2022	10/29/2022	10/30/2022	13	4
12
13	Date	8/23/2022
14	Period	11
15	Week	3
16
17	Date	9/29/2022
18	Period	12
19	Week	4
Sheet1

Cell Formulas
Range	Formula
B2:G11	B2	=A2+1
A3:A11	A3	=G2+1
H4,H8	H4	=H3+1
H5:H7,H9:H11	H5	=H4

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Mon	Tue	Wed	Thurs	Fri	Sat	Sun
2	22/08/2022	23/08/2022	24/08/2022	25/08/2022	26/08/2022	27/08/2022	28/08/2022	11	3
3	29/08/2022	30/08/2022	31/08/2022	01/09/2022	02/09/2022	03/09/2022	04/09/2022	11	4
4	05/09/2022	06/09/2022	07/09/2022	08/09/2022	09/09/2022	10/09/2022	11/09/2022	12	1
5	12/09/2022	13/09/2022	14/09/2022	15/09/2022	16/09/2022	17/09/2022	18/09/2022	12	2
6	19/09/2022	20/09/2022	21/09/2022	22/09/2022	23/09/2022	24/09/2022	25/09/2022	12	3
7	26/09/2022	27/09/2022	28/09/2022	29/09/2022	30/09/2022	01/10/2022	02/10/2022	12	4
8	03/10/2022	04/10/2022	05/10/2022	06/10/2022	07/10/2022	08/10/2022	09/10/2022	13	1
9	10/10/2022	11/10/2022	12/10/2022	13/10/2022	14/10/2022	15/10/2022	16/10/2022	13	2
10	17/10/2022	18/10/2022	19/10/2022	20/10/2022	21/10/2022	22/10/2022	23/10/2022	13	3
11	24/10/2022	25/10/2022	26/10/2022	27/10/2022	28/10/2022	29/10/2022	30/10/2022	13	4
12
13	Date	23/08/2022
14	Period	11
15	Week	3
Data

Cell Formulas
Range	Formula
B2:G11	B2	=A2+1
A3:A11	A3	=G2+1
H4,H8	H4	=H3+1
H5:H7,H9:H11	H5	=H4
B14	B14	=SUMPRODUCT((A2:G11=B13)*(H2:H11))
B15	B15	=SUMPRODUCT((A2:G11=B13)*(I2:I11))

Also which version of Excel are you actually using?

 

[rdbauer83]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Mon	Tue	Wed	Thurs	Fri	Sat	Sun
2	22/08/2022	23/08/2022	24/08/2022	25/08/2022	26/08/2022	27/08/2022	28/08/2022	11	3
3	29/08/2022	30/08/2022	31/08/2022	01/09/2022	02/09/2022	03/09/2022	04/09/2022	11	4
4	05/09/2022	06/09/2022	07/09/2022	08/09/2022	09/09/2022	10/09/2022	11/09/2022	12	1
5	12/09/2022	13/09/2022	14/09/2022	15/09/2022	16/09/2022	17/09/2022	18/09/2022	12	2
6	19/09/2022	20/09/2022	21/09/2022	22/09/2022	23/09/2022	24/09/2022	25/09/2022	12	3
7	26/09/2022	27/09/2022	28/09/2022	29/09/2022	30/09/2022	01/10/2022	02/10/2022	12	4
8	03/10/2022	04/10/2022	05/10/2022	06/10/2022	07/10/2022	08/10/2022	09/10/2022	13	1
9	10/10/2022	11/10/2022	12/10/2022	13/10/2022	14/10/2022	15/10/2022	16/10/2022	13	2
10	17/10/2022	18/10/2022	19/10/2022	20/10/2022	21/10/2022	22/10/2022	23/10/2022	13	3
11	24/10/2022	25/10/2022	26/10/2022	27/10/2022	28/10/2022	29/10/2022	30/10/2022	13	4
12
13	Date	23/08/2022
14	Period	11
15	Week	3
Data

Cell Formulas
Range	Formula
B2:G11	B2	=A2+1
A3:A11	A3	=G2+1
H4,H8	H4	=H3+1
H5:H7,H9:H11	H5	=H4
B14	B14	=SUMPRODUCT((A2:G11=B13)*(H2:H11))
B15	B15	=SUMPRODUCT((A2:G11=B13)*(I2:I11))

Also which version of Excel are you actually using?

That's it thank you

 

[Fluff]

Glad to help & thanks for the feedback.