Create Dynamic list from grid (formula, not VBA)

[GuyGadois]

I have the following grid (simplified) where I would like the user enters a product in F1 (entered in as "Product 1") and the corresponding percentage is displayed on the right. I will use Data Validation for a drop-down list for F1 to make it easy for the user to choose the product and then the parts and percentages are automatically pulled over to F2 and G2. Can anyone help with this?

Cheers,

Guy

	Product 1	Product 2	Product 3		Product 1	<<-Where you enter in Product Name
A	33.33​				A	33.33​	<<- Results
B	0​	33.33​			C	33.33​
C	33.33​	33.33​			D	33.34​
D	33.34​		33.33​
E	0​	33.34​	33.34​
F	0​		33.33​

 

[GuyGadois]

any ideas?

Cheers1

 

[Sufiyan97]

Try

Book2
	B	C	D	E	F	G	H	I	J
1					Product 2
2		Product 1	Product 2	Product 3		<<-Where you enter in Product Name
3	A	33.33				A	33.33	0	<<- Results
4	B	0	33.33			B	33.33	33.33
5	C	33.33	33.33			C	33.34	33.33
6	D	33.34		33.33		D		0
7	E	0	33.34	33.34		E		33.34
8	F	0		33.33		F
Sheet1

Cell Formulas
Range	Formula
I3:I7	I3	=INDEX($C$3:$E$8,MATCH(G3,$B$3:$B$8,0),MATCH($F$1,$C$2:$E$2,0))

 

[GuyGadois]

Thanks for your reply. It is kind of what I am looking for but not getting the end results. The result should be a dynamic list where it shows the "Parts" (Column A) and the percentages for the product Chosen. If there is no "Part" for that product then the Part should not show. For example...

Part	Product 1	Product 2	Product 3		Product 1	%		Product 3	%
A	33.33				A	33.33		D	25
B	0	33.33			C	33.33		E	25
C	33.33	33.33			D	33.34		F	25
D	33.34		25					ZZ	25
E	0	33.34	25
F	0		25
S
ZZ			25

Note that in the example above the yellow text is where the user chooses a Product. Below the Part will be listed if there is a percentage assigned to it. The next column shows the value associated to that part for that product. If the product does not use the part then it will not be shown in the results.

Cheers

Guy

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Part	Product 1	Product 2	Product 3		Product 1	%
2	A	33.33				A	33.33
3	B	0	33.33			C	33.33
4	C	33.33	33.33			D	33.34
5	D	33.34		25
6	E	0	33.34	25
7	F	0		25
8	S
9	ZZ			25
10
11
Main

Cell Formulas
Range	Formula
F2:F8	F2	=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/($B$1:$D$1=$F$1)/($B$2:$D$100>0),ROWS(F$2:F2))),"")
G2:G8	G2	=IF(F2="","",INDEX($B$2:$D$100,MATCH(F2,$A$2:$A$100,0),MATCH($F$1,$B$1:$D$1,0)))