Vlook up an amount for an item and deduct it from that row - issue is that items repeat multiple times - need to take into account the amount already deducted from the same item in the above rows

Hi,

In the attached file I am trying to allocate stock provision to the full stock list, but got an issue where we have same stock item but in a different location - I can't get rid of the location and the provision does not have location allocated to it.

It doesn't really matter to which location the provision is allocated as long as the full amount of it is allocated.

So far, I got to the below formula, but this does not take into account any provision allocated in any of the above rows. In this case issue on Item 1 & Item 5.

Can someone please help me to improve the below formula to take into account the amounts already allocated in the rows above?

=IFERROR(IF(VLOOKUP(A2,'Stock Provision'!$A:$B,2,0)=D2,D2,IF(VLOOKUP(A2,'Stock Provision'!$A:$B,2,0)<D2,(VLOOKUP(A2,'Stock Provision'!$A:$B,2,0)),IF(VLOOKUP(A2,'Stock Provision'!$A:$B,2,0)>D2,D2,0))),0)

Thanks,

Jurate