Dependant Drop Down Lists that Dynamically Change

[NWPhotoExplorer]

Hi! I am new to designing forms in Excel.

I am working on trying to make a form where the user can select the drop-down and select the item (shirt, jacket, etc), then the next drop-down will pull up the various options (color, etc), and the next drop-down will pull up sizes (S, M, L, XL, XXL, etc). Once these 3 drop-downs are selected, it will use vlookup to pull the rest of the data and fill out the rest of the fields on the sheet.

Right now, I can't figure out how to create the drop-down lists to filter out the values and only show the correct items. I don't want to see multiple rows of the same item. I thought I had the first drop down correct, but I can't get it to show anything.

I've tried googling this, but I can't figure out what I am doing wrong. I would appreciate any help I can get.

Thanks!

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	COLOR	SIZE
2							ITEM	COLOR	SIZE	PRICE	MANUFACTURE
3							T-SHIRT	BLUE	SMALL
4							T-SHIRT	BLUE	MEDIUM
5							T-SHIRT	BLUE	LARGE
6							T-SHIRT	BLUE	X-LARGE
7	PRICE	MANUFACTURE					T-SHIRT	BLUE	XX-LARGE
8							T-SHIRT	RED	SMALL
9							T-SHIRT	RED	MEDIUM
10							T-SHIRT	RED	LARGE
11							T-SHIRT	RED	X-LARGE
12							T-SHIRT	RED	XX-LARGE
13							BUTTON SHIRT	BLUE	SMALL
14							BUTTON SHIRT	BLUE	MEDIUM
15							BUTTON SHIRT	BLUE	LARGE
16							BUTTON SHIRT	BLUE	X-LARGE
17							BUTTON SHIRT	BLUE	XX-LARGE
18							BUTTON SHIRT	RED	SMALL
19							BUTTON SHIRT	RED	MEDIUM
20							BUTTON SHIRT	RED	LARGE
21							BUTTON SHIRT	RED	X-LARGE
22							BUTTON SHIRT	RED	XX-LARGE
23							JACKET	BLACK	SMALL
24							JACKET	BLACK	MEDIUM
25							JACKET	BLACK	LARGE
26							JACKET	BLACK	X-LARGE
27							JACKET	BLACK	XX-LARGE
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=UNIQUE(ITEM,FALSE,FALSE)
B2	List	=INDIRECT($A$2)
C2	List	=INDIRECT($B$2)

 

[Fluff]

With 3 helper columns

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ITEM	COLOR	SIZE
2	JACKET	BLACK	SMALL				ITEM	COLOR	SIZE	PRICE	MANUFACTURE		T-SHIRT	BLACK	SMALL
3							T-SHIRT	BLUE	SMALL	1	A		BUTTON SHIRT		MEDIUM
4							T-SHIRT	BLUE	MEDIUM	2	B		JACKET		LARGE
5							T-SHIRT	BLUE	LARGE	3	C				X-LARGE
6							T-SHIRT	BLUE	X-LARGE	4	D				XX-LARGE
7	PRICE	MANUFACTURE					T-SHIRT	BLUE	XX-LARGE	5	E
8	21	U					T-SHIRT	RED	SMALL	6	F
9							T-SHIRT	RED	MEDIUM	7	G
10							T-SHIRT	RED	LARGE	8	H
11							T-SHIRT	RED	X-LARGE	9	I
12							T-SHIRT	RED	XX-LARGE	10	J
13							BUTTON SHIRT	BLUE	SMALL	11	K
14							BUTTON SHIRT	BLUE	MEDIUM	12	L
15							BUTTON SHIRT	BLUE	LARGE	13	M
16							BUTTON SHIRT	BLUE	X-LARGE	14	N
17							BUTTON SHIRT	BLUE	XX-LARGE	15	O
18							BUTTON SHIRT	RED	SMALL	16	P
19							BUTTON SHIRT	RED	MEDIUM	17	Q
20							BUTTON SHIRT	RED	LARGE	18	R
21							BUTTON SHIRT	RED	X-LARGE	19	S
22							BUTTON SHIRT	RED	XX-LARGE	20	T
23							JACKET	BLACK	SMALL	21	U
24							JACKET	BLACK	MEDIUM	22	V
25							JACKET	BLACK	LARGE	23	W
26							JACKET	BLACK	X-LARGE	24	X
27							JACKET	BLACK	XX-LARGE	25	Y
28
Master

Cell Formulas
Range	Formula
M2:M4	M2	=UNIQUE(G3:G27)
N2	N2	=UNIQUE(FILTER(H3:H27,G3:G27=A2))
O2:O6	O2	=UNIQUE(FILTER(I3:I27,(G3:G27=A2)*(H3:H27=B2)))
A8:B8	A8	=FILTER(J3:K27,(G3:G27=A2)*(H3:H27=B2)*(I3:I27=C2),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2:C2	List	=M2#

 

[NWPhotoExplorer]

With 3 helper columns

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ITEM	COLOR	SIZE
2	JACKET	BLACK	SMALL				ITEM	COLOR	SIZE	PRICE	MANUFACTURE		T-SHIRT	BLACK	SMALL
3							T-SHIRT	BLUE	SMALL	1	A		BUTTON SHIRT		MEDIUM
4							T-SHIRT	BLUE	MEDIUM	2	B		JACKET		LARGE
5							T-SHIRT	BLUE	LARGE	3	C				X-LARGE
6							T-SHIRT	BLUE	X-LARGE	4	D				XX-LARGE
7	PRICE	MANUFACTURE					T-SHIRT	BLUE	XX-LARGE	5	E
8	21	U					T-SHIRT	RED	SMALL	6	F
9							T-SHIRT	RED	MEDIUM	7	G
10							T-SHIRT	RED	LARGE	8	H
11							T-SHIRT	RED	X-LARGE	9	I
12							T-SHIRT	RED	XX-LARGE	10	J
13							BUTTON SHIRT	BLUE	SMALL	11	K
14							BUTTON SHIRT	BLUE	MEDIUM	12	L
15							BUTTON SHIRT	BLUE	LARGE	13	M
16							BUTTON SHIRT	BLUE	X-LARGE	14	N
17							BUTTON SHIRT	BLUE	XX-LARGE	15	O
18							BUTTON SHIRT	RED	SMALL	16	P
19							BUTTON SHIRT	RED	MEDIUM	17	Q
20							BUTTON SHIRT	RED	LARGE	18	R
21							BUTTON SHIRT	RED	X-LARGE	19	S
22							BUTTON SHIRT	RED	XX-LARGE	20	T
23							JACKET	BLACK	SMALL	21	U
24							JACKET	BLACK	MEDIUM	22	V
25							JACKET	BLACK	LARGE	23	W
26							JACKET	BLACK	X-LARGE	24	X
27							JACKET	BLACK	XX-LARGE	25	Y
28
Master

Cell Formulas
Range	Formula
M2:M4	M2	=UNIQUE(G3:G27)
N2	N2	=UNIQUE(FILTER(H3:H27,G3:G27=A2))
O2:O6	O2	=UNIQUE(FILTER(I3:I27,(G3:G27=A2)*(H3:H27=B2)))
A8:B8	A8	=FILTER(J3:K27,(G3:G27=A2)*(H3:H27=B2)*(I3:I27=C2),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2:C2	List	=M2#

I added those formulas and I have the drop downs built. For the VLOOKUP, how do I use 3 values to find the right row? It seems like I can only use 1 value and it takes the first matching record.

 

[Fluff]

Use the formula in A8