Data lookup in a fixed position

[BijanBorazjani]

Good Morning,

I have a grid of data in a sheet, all of which is formatted the same. I need to match a week number, and retrieve data offset from the matched cell. The offset will always be the same, one column over and 6 rows down. When i use the offset match combo, it gives me an N/A error. So on the "Calls Total" Sheet, i need it to match Week # and pull the data from the totals cell.

Book1
	A	B	C	D	E	F
1	Week 1	Calls	Tickets	Week 2	Calls	Tickets
2	1/1/2022			1/8/2022
3	1/2/2022			1/9/2022
4	1/3/2022			1/10/2022
5	1/4/2022			1/11/2022
6	1/5/2022			1/12/2022
7	Total	0	0	Total	0	0
8	Week 3	Calls	Tickets	Week 4	Calls	Tickets
9	1/15/2022			1/22/2022
10	1/16/2022			1/23/2022
11	1/17/2022			1/24/2022
12	1/18/2022			1/25/2022
13	1/19/2022			1/26/2022
14	Total	0	0	Total	0	0
Name 1

Cell Formulas
Range	Formula
B7:C7,E14:F14,B14:C14,E7:F7	B7	=SUM(B2:B6)

Book1
	A	B	C	D	E	F
1	Week 1	Calls	Tickets	Week 2	Calls	Tickets
2	1/1/2022			1/8/2022
3	1/2/2022			1/9/2022
4	1/3/2022			1/10/2022
5	1/4/2022			1/11/2022
6	1/5/2022			1/12/2022
7	Total	0	0	Total	0	0
8	Week 3	Calls	Tickets	Week 4	Calls	Tickets
9	1/15/2022			1/22/2022
10	1/16/2022			1/23/2022
11	1/17/2022			1/24/2022
12	1/18/2022			1/25/2022
13	1/19/2022			1/26/2022
14	Total	0	0	Total	0	0
Name 2

Cell Formulas
Range	Formula
B7:C7,E14:F14,B14:C14,E7:F7	B7	=SUM(B2:B6)

Book1
	A	B	C	D
1		Name 1	Name 2	Name 3
2	Week 1	#N/A
3	Week 2
4	Week 3
5	Week 4
Call Totals

Cell Formulas
Range	Formula
B2	B2	=OFFSET('Name 1'!A1,MATCH(A2,'Name 1'!A1:F14,0),5,2)

 

[KRice]

Here is an approach to pull in the "Calls" totals:

Book3
	A	B	C	D
1		Name 1	Name 2	Name 3
2	Week 1	1	14	#REF!
3	Week 2	12	24	#REF!
4	Week 3	3	15	#REF!
5	Week 4	88	9	#REF!
Call Totals

Cell Formulas
Range	Formula
B2:D5	B2	=LET(arry,INDIRECT("'"&B$1&"'!"&"$A$1:$F$14"), r,ROWS(arry),c,COLUMNS(arry),wkpos,MAX(IF(arry=$A2,SEQUENCE(r,c))),valpos,wkpos+6*c+1,INDEX(arry,INT(valpos/c)+1,MOD(valpos,c)))

 

[KRice]

I discovered an error in the last step of the formula I posted. Here is a corrected version (green) for returning the Call Totals and the Ticket Totals. Another option (blue) does the same, but finds the value to return a little differently:

MrExcel_20220805.xlsx
	A	B	C
30	Call Totals	Name 1	Name 2
31	Week 1	1	14
32	Week 2	12	24
33	Week 3	3	15
34	Week 4	88	9
35
36	Tickets Totals	Name 1	Name 2
37	Week 1	4	0
38	Week 2	99	0
39	Week 3	9	0
40	Week 4	999	0
41
42	Call Totals	Name 1	Name 2
43	Week 1	1	14
44	Week 2	12	24
45	Week 3	3	15
46	Week 4	88	9
47
48	Tickets Totals	Name 1	Name 2
49	Week 1	4	0
50	Week 2	99	0
51	Week 3	9	0
52	Week 4	999	0
Call Totals

Cell Formulas
Range	Formula
B31:C34	B31	=LET(arry,INDIRECT("'"&B$1&"'!"&"$A$1:$F$14"), r,ROWS(arry),c,COLUMNS(arry),wkpos,MAX(IF(arry=$A31,SEQUENCE(r,c))),valpos,wkpos+6*c+1,INDEX(arry,QUOTIENT(valpos-1,c)+1,MOD(valpos-1,c)+1))
B37:C40	B37	=LET(arry,INDIRECT("'"&B$1&"'!"&"$A$1:$F$14"), r,ROWS(arry),c,COLUMNS(arry),wkpos,MAX(IF(arry=$A37,SEQUENCE(r,c))),valpos,wkpos+6*c+2,INDEX(arry,QUOTIENT(valpos-1,c)+1,MOD(valpos-1,c)+1))
B43:C46	B43	=LET(arry,INDIRECT("'"&B$1&"'!"&"$A$1:$F$14"), r,ROWS(arry),c,COLUMNS(arry),wkpos,MAX(IF(arry=$A43,SEQUENCE(r,c))),valpos,wkpos+6*c+1,MAX(IF(SEQUENCE(r,c)=valpos,arry,"")))
B49:C52	B49	=LET(arry,INDIRECT("'"&B$1&"'!"&"$A$1:$F$14"), r,ROWS(arry),c,COLUMNS(arry),wkpos,MAX(IF(arry=$A49,SEQUENCE(r,c))),valpos,wkpos+6*c+2,MAX(IF(SEQUENCE(r,c)=valpos,arry,"")))