How to get Index Match to look across all worksheets?

[IrisDiane]

Hello! Im attempting to use Index Match to return data from all my worksheets (there will be 50-75 in the end) based on selection from a drop down list. I have it working for one sheet just not all of them. I was hoping to maybe use a SheetList although I cannot figure out how to do this. Any help would be greatly appreciated!

P Helper(1480).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Lemon													SheetList
2														355
3														454
4	F	Date Used	AI	C2										407
5	355	Oct-30-20	Okay	Free										405
6	355	Feb-22-21	Okay	Free
7	355	Jan-22-20	Okay	Free
8	355	Mar-12-20	Okay	Free
9	355	June-30-20	Okay	Free
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Desired Result

Cell Formulas
Range	Formula
B5:B27	B5	=IFERROR(INDEX('355'!C:C,AGGREGATE(15,6,ROW('355'!C$3:C$121)/('355'!B$3:B$121=A$1),ROWS(B$5:B5))),"")
C5:C27	C5	=IFERROR(INDEX('355'!L:L,AGGREGATE(15,6,ROW('355'!L$3:L$121)/('355'!B$3:B$121=A$1),ROWS(C$5:C5))),"")
D5:D27	D5	=IFERROR(INDEX('355'!K:K,AGGREGATE(15,6,ROW('355'!K$3:K$121)/('355'!B$3:B$121=A$1),ROWS(D$5:D5))),"")
A5	A5	=IFERROR(INDEX('355'!A:A,AGGREGATE(15,6,ROW('355'!A$3:A$121)/('355'!B$3:B$121=A$1),ROWS(A$5:A5))),"")
A6:A27	A6	=IFERROR(INDEX('355'!A:A,AGGREGATE(15,6,ROW('355'!A$3:A$121)/('355'!B$3:B$121=A$1),ROWS(A$5:A6))),"")
A28:A30	A28	=IFERROR(INDEX('355'!A:A,AGGREGATE(15,6,ROW('355'!A$3:A$121)/('355'!B$3:B$121=A$1),ROWS(#REF!))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1:B2	List	=Data!$B$2:$B$13

P Helper(1480).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Number: 355		Name:
2	F	P	Date	MC	MU	MJ	MC2	MK	MA	ME	C2	AI
3	355	Grape	Jan-30-21	0.1	0.8	1.1	0.12	4.5	0	0	Free	Good
4	355	Grape	Jan-30-21	0.1	0.8	1.1	0.12	4.5	0	0	Free	Good
5	355	Mango	Jan-30-21	0.9	2.1	6.5	9.1	8.7	5.5	0.3	Free	Poor
6	355	Lemon	Oct-30-20	0.55	2.9	9.9	5.6	1.9	8.9	0.3	Free	Okay
7	355	Orange	Jan-19-21	0.2	8.2	4.2	0.1	2.3	5.5	2.2	Final	Fine
8	355	Grape	Feb-22-21	0.1	0.8	1.1	0.12	4.5	0	0	Free	Good
9	355	Lemon	Feb-22-21	0.55	2.9	9.9	5.6	1.9	8.9	0.3	Free	Okay
10	355	Lime	Mar-20-21	0.23	9.1	9.1	7.14	5.1	5.1	8.9	Final	Poor
11	355	Apple	June-20-21	0.01	0.2	0.1	0.1	0.05	0.05	0.1	Final	Great
12	355	Lemon	Jan-22-20	0.55	2.9	9.9	5.6	1.9	8.9	0.3	Free	Okay
13	355	Lemon	Mar-12-20	0.55	2.9	9.9	5.6	1.9	8.9	0.3	Free	Okay
14	355	Lemon	June-30-20	0.55	2.9	9.9	5.6	1.9	8.9	0.3	Free	Okay
15	355
355

Cell Formulas
Range	Formula
D3:D15	D3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,4,FALSE),"")
E3:E15	E3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,5,FALSE),"")
F3:F15	F3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,6,FALSE),"")
G3:G15	G3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,7,FALSE),"")
H3:H15	H3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,8,FALSE),"")
I3:I15	I3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,9,FALSE),"")
J3:J15	J3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,10,FALSE),"")
K3:K15	K3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,2,FALSE),"")
L3:L15	L3	=IFERROR(VLOOKUP(B3,Data!B$2:M$13,3,FALSE),"")
C15	C15	=IFERROR(VLOOKUP(B15,Data!B$2:M$13,2,FALSE),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:J167	Cell Value	between 0 and 0.05	text	NO
G4:G167	Cell Value	between 0 and 0.05	text	NO
F4:F167	Cell Value	between 0 and 0.05	text	NO
E4:E167	Cell Value	between 0 and 0.05	text	NO
D4:D167	Cell Value	between 0 and 0.05	text	NO
B4:B167	Cell Value	ending with "*"	text	NO
B4:B167	Cell Value	ending with "'"	text	NO
B4:B167	Cell Value	ending with ","	text	NO
B4:B167	Cell Value	ending with "."	text	NO
D3:J3	Cell Value	between 0 and 0.05	text	NO
B3	Cell Value	ending with "*"	text	NO
B3	Cell Value	ending with "'"	text	NO
B3	Cell Value	ending with ","	text	NO
B3	Cell Value	ending with "."	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3:B15	List	=Data!$B$2:$B$20

 

[jasonb75]

Im attempting to use Index Match to return data from all my worksheets (there will be 50-75 in the end) based on selection from a drop down list.

Attempting the impossible would be an understatement here. Assuming that 'Lemon' is the dropdown selection and that '355' is one of the 50-75 sheets that you want to pull results from, I would say that what you are attempting is extremely unrealistic.

I would say that 3-4 sheets would be the limit for formulas to do this, maybe slightly more with office 365 if efficiency can be improved by some of the newer functions, even then it would be a case of possible but not practical.