I am attempting to collect funds from 3rd party vendors based on their yearly purchase amount. I am given a total target that needs to be split across merch. divisions but weighted based on their historical purchases for that MD. I know I can use a vlookup with a countif to divide based on occurrence of the account number but I need to take it a step further.

Example:
Vendor 5 has a total target of $43M , I need to split that total across 3 divisions by the historical % (for kitchen, 18% of $43M would be the target $)
[![enter image description here](https://i.stack.imgur.com/IEAk5.png)](https://i.stack.imgur.com/IEAk5.png)

is there a formula that will automatically do this?