INDEX MATCH with Multiple Criteria and Multiple Sheets

[Lalalauren191]

So maybe I need to use a different formula entirely, but here is my data first and then my question.

• Location is cell B2
• Month is cell B3
• Sheets that the data is being pulled from are:
  • GSP D18
  • GSP D35
  • GSP D110
  • GSP D121
• Index range on each sheet is B5:BQ17
• Match Range on each sheet for location names is A5:A17
• Match Range on each sheet for month is B3:BQ3
• My full current formula is: =INDEX('GSP D18'!$B$5:$BQ$17,MATCH($B$2,'GSP D18'!$A$5:$A$17,0),MATCH($B$3,'GSP D18'!$B$3:$BQ$3,0))

My issue is that this formula works to pull data from GSP D18, but I cannot figure out a way to get it to look at multiple sheets. Ultimately I need a formula that will look for a location number on each sheet and return data based on both the month and location since it is a trend file. It is not possible to add all the information to 1 sheet, since each sheet contains so much additional information that is not in the range provided.

Hopefully this was enough information.

 

[StephenCrump]

Perhaps something along these lines? Workbook here: Example.xls

Match across dynamic ranges, multiple sheets.xlsx
	A	B	C	D	E	F	G	H
1	User Input:			Location	Sheet name		SheetList	Count
2	MyLocation	502		426	GSP D18		GSP D18	9
3	MyTrend	Trend2		502	GSP D35		GSP D35	7
4	MyMonth	1/02/2020		504	GSP D18
5				505	GSP D18
6	Helper cell			668	GSP D35
7	MySheet	'GSP D35'!		776	GSP D35
8				1466	GSP D35
9	Result	26		1515	GSP D18
10				1	GSP D18
11				2	GSP D18
12				3	GSP D18
13				4	GSP D18
14				5	GSP D18
15				11	GSP D35
16				12	GSP D35
17				13	GSP D35
MASTER

Cell Formulas
Range	Formula
H2:H3	H2	=COUNTIF(INDEX(LocationTable,,2),G2)
B7	B7	="'"&VLOOKUP(B2,LocationTable,2,)&"'!"
B9	B9	=VLOOKUP(MyLocation,INDEX(INDIRECT(MySheet&"$A:$A"),MATCH(MyTrend,INDIRECT(MySheet&"$A:$A"),)+1):INDEX(INDIRECT(MySheet&"$F:$F"),MATCH(MyTrend,INDIRECT(MySheet&"$A:$A"),)+VLOOKUP(VLOOKUP(B2,LocationTable,2,),SheetList,2)),1+MATCH(MyMonth,MyDates,),)
E2:E17	E2	=INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&INDEX(SheetList,,1)&"'!A$5:A$17"),D2)^0,),1)

Named Ranges
Name	Refers To	Cells
LocationTable	=MASTER!$D$2:$E$17	B9, E2, B7, H2:H3
MyLocation	=MASTER!$B$2	B9, B7
MyMonth	=MASTER!$B$4	B9
MySheet	=MASTER!$B$7	B9
MyTrend	=MASTER!$B$3	B9
SheetList	=MASTER!$G$2:$H$3	B9, E2:E17, H2

Match across dynamic ranges, multiple sheets.xlsx
	A	B	C	D	E	F
3	Site	Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020
4	Trend1
5	505	60	15	79	18	38
6	426	34	75	98	63	11
7	1515	60	1	30	13	33
8	504	1	18	100	15	11
9	1	60	42	37	98	92
10	2	67	24	71	63	37
11	3	84	97	8	50	63
12	4	43	68	65	78	55
13	5	98	19	68	74	23
14
15	Trend2
16	505	79	77	88	73	30
17	426	80	32	79	46	7
18	1515	45	5	72	62	16
19	504	62	6	27	85	28
20	1	92	81	55	71	44
21	2	50	76	33	37	99
22	3	33	71	26	40	70
23	4	21	79	90	83	75
24	5	5	69	63	47	84
GSP D18

Match across dynamic ranges, multiple sheets.xlsx
	A	B	C	D	E	F
3	Site	Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020
4	Trend1
5	776	90	2	87	62	15
6	668	28	15	53	82	2
7	1466	1	55	16	52	32
8	502	13	58	1	98	55
9	11	52	48	27	26	57
10	12	51	58	25	31	85
11	13	53	74	63	10	64
12
13	Trend2
14	776	67	99	21	74	84
15	668	15	8	71	79	50
16	1466	42	64	99	33	81
17	502	87	26	62	13	100
18	11	46	19	69	97	57
19	12	18	68	71	47	17
20	13	1	52	93	29	26
GSP D35

 

[Lalalauren191]

Perhaps something along these lines? Workbook here: Example.xls

Match across dynamic ranges, multiple sheets.xlsx
	A	B	C	D	E	F	G	H
1	User Input:			Location	Sheet name		SheetList	Count
2	MyLocation	502		426	GSP D18		GSP D18	9
3	MyTrend	Trend2		502	GSP D35		GSP D35	7
4	MyMonth	1/02/2020		504	GSP D18
5				505	GSP D18
6	Helper cell			668	GSP D35
7	MySheet	'GSP D35'!		776	GSP D35
8				1466	GSP D35
9	Result	26		1515	GSP D18
10				1	GSP D18
11				2	GSP D18
12				3	GSP D18
13				4	GSP D18
14				5	GSP D18
15				11	GSP D35
16				12	GSP D35
17				13	GSP D35
MASTER

Cell Formulas
Range	Formula
H2:H3	H2	=COUNTIF(INDEX(LocationTable,,2),G2)
B7	B7	="'"&VLOOKUP(B2,LocationTable,2,)&"'!"
B9	B9	=VLOOKUP(MyLocation,INDEX(INDIRECT(MySheet&"$A:$A"),MATCH(MyTrend,INDIRECT(MySheet&"$A:$A"),)+1):INDEX(INDIRECT(MySheet&"$F:$F"),MATCH(MyTrend,INDIRECT(MySheet&"$A:$A"),)+VLOOKUP(VLOOKUP(B2,LocationTable,2,),SheetList,2)),1+MATCH(MyMonth,MyDates,),)
E2:E17	E2	=INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&INDEX(SheetList,,1)&"'!A$5:A$17"),D2)^0,),1)

Named Ranges
Name	Refers To	Cells
LocationTable	=MASTER!$D$2:$E$17	B9, E2, B7, H2:H3
MyLocation	=MASTER!$B$2	B9, B7
MyMonth	=MASTER!$B$4	B9
MySheet	=MASTER!$B$7	B9
MyTrend	=MASTER!$B$3	B9
SheetList	=MASTER!$G$2:$H$3	B9, E2:E17, H2

Match across dynamic ranges, multiple sheets.xlsx
	A	B	C	D	E	F
3	Site	Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020
4	Trend1
5	505	60	15	79	18	38
6	426	34	75	98	63	11
7	1515	60	1	30	13	33
8	504	1	18	100	15	11
9	1	60	42	37	98	92
10	2	67	24	71	63	37
11	3	84	97	8	50	63
12	4	43	68	65	78	55
13	5	98	19	68	74	23
14
15	Trend2
16	505	79	77	88	73	30
17	426	80	32	79	46	7
18	1515	45	5	72	62	16
19	504	62	6	27	85	28
20	1	92	81	55	71	44
21	2	50	76	33	37	99
22	3	33	71	26	40	70
23	4	21	79	90	83	75
24	5	5	69	63	47	84
GSP D18

Match across dynamic ranges, multiple sheets.xlsx
	A	B	C	D	E	F
3	Site	Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020
4	Trend1
5	776	90	2	87	62	15
6	668	28	15	53	82	2
7	1466	1	55	16	52	32
8	502	13	58	1	98	55
9	11	52	48	27	26	57
10	12	51	58	25	31	85
11	13	53	74	63	10	64
12
13	Trend2
14	776	67	99	21	74	84
15	668	15	8	71	79	50
16	1466	42	64	99	33	81
17	502	87	26	62	13	100
18	11	46	19	69	97	57
19	12	18	68	71	47	17
20	13	1	52	93	29	26
GSP D35

You are a genius. Thank you!