Short out short stock item.

[shohagppl]

Product	Component	Stock	Required Qty	Short/Excess
A1	z1	22	31	-9
A1	y1	40	40	0
A1	x1	32	21	11
A1	u1	43	40	3
A1	v1	37	43	-6
B1	c1	30	35	-5
B1	z1	22	31	-9
B1	k1	27	36	-9
B1	x1	32	21	11
B1	m1	32	38	-6
B1	n1	42	34	8

Short Materials:

A1	z1,v1
B1	c1,z1,k1,m1

I'm using office 2019.

[#2]

@shohagppl 

For this task you can use the FILTER() function in combination with TEXTJOIN().

G6 = A1

=TEXTJOIN(", ",TRUE,FILTER($B$2:$B$12,($E$2:$E$12<0)*($A$2:$A$12=$G6)))

[#3]

Thank you for your comment @dscheikey . But I'm using Excel 2019 version where FILTER function not available.

[#4 · Accepted solution]

@shohagppl 

In Excel 2019, you can use an array formula confirmed by pressing Ctrl+Shift+Enter:

The products A1 and B1 are in A15:A16 in the screenshot above. The formula in B15 is

=TEXTJOIN(",",TRUE,IF(($A$2:$A$12=A15)*($E$2:$E$12<0),$B$2:$B$12,""))

[#5]

@shohagppl 

@Hans Vogelaarshowed you how to do it without the FILTER() function. I could not have known that Excel 2019 does not have a FILTER() function. I looked up the filter function on the help page beforehand. The availability in Excel 2019 is shown there.

Help

[#6]

Thanks @Hans Vogelaar & dscheikey. Because I've learn two way.

[#7]

Thank you for your support.