Pull data based on another column

[maabbas]

Hello everyone

I need little help for solving a small problem. below worksheet is out of our Bill of Material formula, if we want to produce a Product '55500' we will need input 500,510,520,530,7600.

7600 is our WIP which also has input 10,20,30,40. i need a formula in next column which add OUTPUT number like in column TEST

[TABLE="width: 500"]
<tbody>[TR]
[TD]OUTPUT[/TD]
[TD]INPUT[/TD]
[TD]TEST[/TD]
[/TR]
[TR]
[TD]55500[/TD]
[TD]500
[/TD]
[TD]7600[/TD]
[/TR]
[TR]
[TD]55500[/TD]
[TD]510[/TD]
[TD]7600[/TD]
[/TR]
[TR]
[TD]55500[/TD]
[TD]520[/TD]
[TD]7600[/TD]
[/TR]
[TR]
[TD]55500[/TD]
[TD]530[/TD]
[TD]7600[/TD]
[/TR]
[TR]
[TD]55500[/TD]
[TD]7600[/TD]
[TD]7600[/TD]
[/TR]
[TR]
[TD]7600[/TD]
[TD]10[/TD]
[TD]55500[/TD]
[/TR]
[TR]
[TD]7600[/TD]
[TD]20[/TD]
[TD]55500[/TD]
[/TR]
[TR]
[TD]7600[/TD]
[TD]30[/TD]
[TD]55500[/TD]
[/TR]
[TR]
[TD]7600[/TD]
[TD]40[/TD]
[TD]55500[/TD]
[/TR]
</tbody>[/TABLE]