Please help me with my workbook as Iam novice to Excel.

[sml_bsni]

Please help me with my workbook as Iam novice to Excel.

I have 2 sheets, "STOCKS" and "ORDERLIST" .....I want to automate in Allocation Column the email address of the client matching the criteria of Product_Name,Size and Gender. its should return the email address as unique value, is drop down list possible? as it will facilitate me to choose whom to send. whatever is possible please help me with this formulae.

Purchase No.	Qty Purchased	Product_Name	Size	Gender	Color	Material	Allocation	Stock in Hand
25962​	2​	Low_rise_Pants	48​	Womens	Blue	Jeans		1​
25962​	3​	Low_rise_Pants	46​	Womens	Blue	Jeans		2​
25962​	4​	Low_rise_Pants	44​	Womens	Blue	Jeans		3​
25962​	1​	Low_rise_Pants	42​	Womens	Blue	Jeans		0​
25962​	5​	Low_rise_Pants	40​	Womens	Blue	Jeans		4​
25962​	2​	Low_rise_Pants	38​	Womens	Blue	Jeans		1​
25962​	1​	Low_rise_Pants	36​	Womens	Blue	Jeans		0​
25962​	1​	Low_rise_Pants	34​	Womens	Blue	Jeans		0​
25962​	1​	Low_rise_Pants	32​	Womens	Blue	Jeans		0​
25962​	1​	Low_rise_Pants	30​	Womens	Blue	Jeans		#VALUE!​
25962​	1​	Low_rise_Pants	28​	Womens	Blue	Jeans		#VALUE!​
31801​	3​	Oversized_shirt	S	Mens	Multi	Cotton		#VALUE!​
31801​	3​	Oversized_shirt	M	Mens	Multi	Cotton		#VALUE!​

Fname	Lname	Email	Product_Name	Size	Gender	QTY	Status
Cash	Parker	Cash@super-fashion.com	Low_rise_Pants	48​	Womens	1​	NOT SENT
Braxton	Burch	Braxton@LemonGreen.Fashion	Oversized_shirt	S	Mens	1​	NOT SENT
Nelson	Ortiz	Nelson@deskon.com	Low_rise_Pants	28​	Womens	1​	NOT SENT
Jaydin	Banks	Jaydin@fucsiatrend.com	Bermuda_shorts	M	Womens	1​	NOT SENT
Andy	Ball	Andy@123fashionista.com	Button_down	XS	Mens	1​	NOT SENT
Raegan	Hood	Raegan@weartear.com	Bermuda_shorts	S	Womens	1​	NOT SENT
April	Best	April@smartcasual.wears	Low_rise_Pants	48​	Womens	1​	NOT SENT
Xavier	Vazquez	Xavier@strretD.on	Low_rise_Pants	46​	Womens	1​	NOT SENT
Cash	Parker	Cash@super-fashion.com	Oversized_shirt	S	Mens	1​	NOT SENT

Thanks

Files below:
Workbook One Drive

 

[kevin9999]

See if this gets you close:

INV Mngmt.xlsx
	A	B	C	D	E	F	G	H	I
1	Fname	Lname	Email	Product_Name	Size	Gender	QTY	Status
2	Jaydin	Banks	Jaydin@fucsiatrend.com	Bermuda_shorts	M	Womens	1	NOT SENT
3	Raegan	Hood	Raegan@weartear.com	Bermuda_shorts	S	Womens	1	NOT SENT
4	Andy	Ball	Andy@123fashionista.com	Button_down	XS	Mens	1	NOT SENT
5	Cash	Parker	Cash@super-fashion.com	Low_rise_Pants	48	Womens	1	NOT SENT
6	Nelson	Ortiz	Nelson@deskon.com	Low_rise_Pants	28	Womens	1	NOT SENT
7	April	Best	April@smartcasual.wears	Low_rise_Pants	48	Womens	1	NOT SENT
8	Xavier	Vazquez	Xavier@strretD.on	Low_rise_Pants	46	Womens	1	NOT SENT
9	Braxton	Burch	Braxton@LemonGreen.Fashion	Oversized_shirt	S	Mens	1	NOT SENT
10	Cash	Parker	Cash@super-fashion.com	Oversized_shirt	S	Mens	1	NOT SENT
11
12
13	Purchase No.	Qty Purchased	Product_Name	Size	Gender	Color	Material	Allocation	Stock in Hand
14	25962	2	Low_rise_Pants	48	Womens	Blue	Jeans	Cash@super-fashion.com	1
15	25962	3	Low_rise_Pants	46	Womens	Blue	Jeans	Xavier@strretD.on	2
16	25962	4	Low_rise_Pants	44	Womens	Blue	Jeans	No match found	3
17	25962	1	Low_rise_Pants	42	Womens	Blue	Jeans	No match found	0
18	25962	5	Low_rise_Pants	40	Womens	Blue	Jeans	No match found	4
19	25962	2	Low_rise_Pants	38	Womens	Blue	Jeans	No match found	1
20	25962	1	Low_rise_Pants	36	Womens	Blue	Jeans	No match found	0
21	25962	1	Low_rise_Pants	34	Womens	Blue	Jeans	No match found	0
22	25962	1	Low_rise_Pants	32	Womens	Blue	Jeans	No match found	0
23	25962	1	Low_rise_Pants	30	Womens	Blue	Jeans	No match found
24	25962	1	Low_rise_Pants	28	Womens	Blue	Jeans	Nelson@deskon.com
25	31801	3	Oversized_shirt	S	Mens	Multi	Cotton	Braxton@LemonGreen.Fashion
26	31801	3	Oversized_shirt	M	Mens	Multi	Cotton	No match found
27
Client Order List

Cell Formulas
Range	Formula
H14:H26	H14	=IFERROR(XLOOKUP(1,(Table1[Product_Name]=C14)*(Table1[Size]=D14)*(Table1[Gender]=E14),Table1[Email]),"No match found")

Cells with Data Validation
Cell	Allow	Criteria
H2:H10	List	--,SENT,PENDING,NOT SENT
D2:D10	List	=STOCKS!$C$2:$C$22
E2:E10	List	=STOCKS!$D$2:$D$22
F2:F10	List	=STOCKS!$E$2:$E$22