Lookup/Indexing formula issue

[Sandman1985]

Hi All,

Happy new year. Hope you're well.

I am struggling with a lookup formula. I have a data table with dates as column headers which goes on for years by day.
Each column has data with activities planned for that day - so multiple lookup values in that column.

In a different sheet, I have a summary table where the user has a weekly summary for whichever given week they want to see the activities for. Again, the column headers are dates.
I'm trying to get the summary to populate with the data from the data table (summary is dynamic - dates can be changed).

My formula looks like this:
=INDEX('Scheduler View'!$A$2:$ARG$2,0,MATCH('Avail Roster'!E$6,'Scheduler View'!$A$3:$ARG$42,0))
This is the dates in the data table This is the date in the summary This is the data

Not sure where I am going wrong, any help would be appreciated.

Regards,

Sandman

 

[Sufiyan97]

I think match works with range and not array.

Can you post your sample data using xl2bb

 

[Sandman1985]

I tried to download xl2bb add in but the IT policy blocked me.

I've attached some screen shots if that helps.

This is what the data sheet looks like:

This is the summary I'm trying to create:

Does this help at all?

Regards,

Sandman

 

[Sufiyan97]

Try

Modify to suite your range

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1/11/2023	1/12/2023	1/13/2023	1/14/2023	1/15/2023
2	50	77	56	69	58
3	99	90	84	54	96
4	88	82	56	80	72
5	60	55	77	76	99			01/15/23
6	66	51	72	86	75			01/11/2023	01/12/2023	01/13/2023	01/14/2023	01/15/2023
7	59	86	65	71	82			50	77	56	69	58
8	83	96	96	65	62			99	90	84	54	96
9	50	85	76	67	68			88	82	56	80	72
10	98	88	56	92	70			60	55	77	76	99
11								66	51	72	86	75
12								59	86	65	71	82
13								83	96	96	65	62
14								50	85	76	67	68
15								98	88	56	92	70
16
17
Sheet1

Cell Formulas
Range	Formula
H6:L15	H6	=FILTER(A1:E10,(A1:E1>=H5-4))
Dynamic array formulas.

Change -4 to -6 if you want to retrive data for 7 days.

 

[Sandman1985]

Try

Modify to suite your range

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1/11/2023	1/12/2023	1/13/2023	1/14/2023	1/15/2023
2	50	77	56	69	58
3	99	90	84	54	96
4	88	82	56	80	72
5	60	55	77	76	99			01/15/23
6	66	51	72	86	75			01/11/2023	01/12/2023	01/13/2023	01/14/2023	01/15/2023
7	59	86	65	71	82			50	77	56	69	58
8	83	96	96	65	62			99	90	84	54	96
9	50	85	76	67	68			88	82	56	80	72
10	98	88	56	92	70			60	55	77	76	99
11								66	51	72	86	75
12								59	86	65	71	82
13								83	96	96	65	62
14								50	85	76	67	68
15								98	88	56	92	70
16
17
Sheet1

Cell Formulas
Range	Formula
H6:L15	H6	=FILTER(A1:E10,(A1:E1>=H5-4))
Dynamic array formulas.

Change -4 to -6 if you want to retrive data for 7 days.

Sufiyan97 that works perfectly.
Thank you so much. Appreciate your help.

 

[Sufiyan97]

You're welcome.
Happy to help.

 

[Sufiyan97]

Hi
If you want to retrive always past 7 days data

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	1/1/2023	1/2/2023	1/3/2023	1/4/2023	1/5/2023	1/6/2023	1/7/2023	1/8/2023	1/9/2023	1/10/2023	1/11/2023	1/12/2023	1/13/2023	1/14/2023	1/15/2023	1/16/2023	1/17/2023	1/18/2023	1/19/2023	1/20/2023	1/21/2023	1/22/2023	1/23/2023	1/24/2023	1/25/2023	1/26/2023
2	50	77	56	69	58	69	50	44	95	67	87	94	23	24	20	32	42	56	97	16	62	74	15	75	37	63
3	99	90	84	54	96	54	95	72	46	82	13	11	31	78	63	55	86	21	26	99	73	97	92	56	65	31
4	88	82	56	80	72	80	99	56	74	33	77	53	64	91	20	45	86	89	66	38	59	53	66	76	39	46
5	60	55	77	76	99	76	56	87	11	52	27	51	87	56	94	14	12	21	96	35	33	93	97	62	27	91
6	66	51	72	86	75	86	58	17	80	33	36	50	89	60	16	28	36	41	58	40	32	93	70	14	30	55
7	59	86	65	71	82	71	11	67	55	78	54	35	19	51	51	62	11	58	76	53	25	12	36	76	60	21
8	83	96	96	65	62	65	18	85	71	15	97	21	70	88	50	33	27	75	53	21	99	36	88	31	82	97
9	50	85	76	67	68	67	72	92	70	69	59	89	91	96	12	79	91	86	80	61	75	60	12	25	29	41
10	98	88	56	92	70	92	92	12	84	19	36	55	88	22	80	36	93	83	71	97	84	40	80	79	53	44
11
12								1/15/2023
13								1/9/2023	1/10/2023	1/11/2023	1/12/2023	1/13/2023	1/14/2023	1/15/2023
14								95	67	87	94	23	24	20
15								46	82	13	11	31	78	63
16								74	33	77	53	64	91	20
17								11	52	27	51	87	56	94
18								80	33	36	50	89	60	16
19								55	78	54	35	19	51	51
20								71	15	97	21	70	88	50
21								70	69	59	89	91	96	12
22								84	19	36	55	88	22	80
23
Sheet1

Cell Formulas
Range	Formula
H13:N22	H13	=FILTER(A1:Z10,(A1:Z1>=H12-6)*(A1:Z1<=H12))
Dynamic array formulas.