Formula required to Distribute Invoice Payment Amount to Invoice Lines using VLOOKUP|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hey Everyone, I require a certain solution to this use case;

*Sample Data attached*

-I have a tab 'Invoices' where Invoice Name and Invoice Line Name are given along with the Invoice Line Amount in the third column of Invoice Tab.

-The second tab "Payment Amount" tab holds the payment amount for each Invoice. 

- I want to distribute the Payment amount to each of the Invoice Lines corresponding with the Invoice Name.

So Ideally the payment amount has to be distributed in a way where the first Payment Amount 1 of Invoice 1 will fill the 'distributed amount' cell with the amount until it has reached the Invoice line Amount, and then move on to the next Invoice line of Invoice 1 and fill the 'distributed amount' cell until its Invoice Line amount has been reached. I need to do this till the Payment Amount for Invoice 1 gets over and the rest of Invoice lines can be made to zero if remaining Payment Amount is 0.

I need to have an expandable formula because I will be dealing with about 10000 rows of Invoice Line. So hopefully I can just drag it till the end of all Invoice Lines.

Hope I gave some clarity on the problem,

Cheers,

Kevin