Dynamic List created with variable

[GuyGadois]

I have the following sheet. The source data is listed in columns A:C (in reality the length of this list is 2000 rows long). I am attempting to make a dynamic list located in E:G (under the green heading) where the dynamic list only includes "models" in J1 (heightened in yellow). In the example, the user picks the model from J1 (Data Validation) and the Dynamic list appears under the green heading only including the model name contained in J1. How would I accomplish this? I am using Excel 2019

Thank you very much

GG

Example.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Model	Fruit	Number		Model	Fruit	Number		Model Name:	A
2	A	Apple	1		A	Apple	1
3	B	Lime	8		A	Lime	2
4	C	Apricot	6		A	Apricot	5
5	D	Lemon	3		A	Lemon	4
6	A	Lime	2
7	B	Apricot	6
8	C	Lemon	8
9	D	Apricot	4
10	A	Apricot	5
11	B	Lemon	4
12	C	Lime	8
13	D	Apricot	6
14	A	Lemon	4
15	B	Apricot	6
16	C	Lemon	3
17	D	Lime	8
Sheet2

 

[Peter_SSs]

Try this

22 07 21.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Model	Fruit	Number		Model	Fruit	Number		Model Name:	A
2	A	Apple	1		A	Apple	1
3	B	Lime	8		A	Lime	2
4	C	Apricot	6		A	Apricot	5
5	D	Lemon	3		A	Lemon	4
6	A	Lime	2
7	B	Apricot	6
8	C	Lemon	8
9	D	Apricot	4
10	A	Apricot	5
11	B	Lemon	4
12	C	Lime	8
13	D	Apricot	6
14	A	Lemon	4
15	B	Apricot	6
16	C	Lemon	3
17	D	Lime	8
List

Cell Formulas
Range	Formula
E2:E17	E2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$3000)/($A$2:$A$3000=$J$1),ROWS(E$2:E2))),"")
F2:G17	F2	=IF($E2="","",INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$3000)/($A$2:$A$3000=$J$1),ROWS(F$2:F2))))

 

[Peter_SSs]

If you don't mind using a helper column you could reduce the calculation burden like this.
Copy the column K formula down to the end of the data (or at least far enough to cover any list that is likely to appear in columns E:G) and then (if you want) hide column K
Then the simpler formula in E2 can be copied across and down as far as the column K formulas go.

22 07 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Model	Fruit	Number		Model	Fruit	Number		Model Name:	A
2	A	Apple	1		A	Apple	1				2
3	B	Lime	8		A	Lime	2				6
4	C	Apricot	6		A	Apricot	5				10
5	D	Lemon	3		A	Lemon	4				14
6	A	Lime	2
7	B	Apricot	6
8	C	Lemon	8
9	D	Apricot	4
10	A	Apricot	5
11	B	Lemon	4
12	C	Lime	8
13	D	Apricot	6
14	A	Lemon	4
15	B	Apricot	6
16	C	Lemon	3
17	D	Lime	8
List (2)

Cell Formulas
Range	Formula
E2:G17	E2	=IF($K2="","",INDEX(A:A,$K2))
K2:K17	K2	=IFERROR(AGGREGATE(15,6,ROW(A$2:A$3000)/(A$2:A$3000=J$1),ROWS(K$2:K2)),"")