Trying to fill cells in order by known rank until overall criteria is met.

Hello,

I am attempting to fill cells based off a ranking system that I've created. I have values with an associated rank and I want to reference those ranks, then fill those values by lowest rank first, then next lowest rank, then next lowest and so on. My Excel sheet has an input for overall sum of values and I want to fill in those individual values by rank until my overall sum is reached or my values are maxed out. My file is attached.

To explain this in terms of my file, I want to input a value into cell F2 and then run a macro that fills in values in Cells F5:F26 based off their ranking system in cells J5:J26, wherein the lowest rank is filled in first and then the next lowest and so on. In this case, the lowest rank is 1.0029, as found in cell M5, and matching that finding to cells J5:J26 (in this case cell J23 matches) would then insert a value of 90 into cell F23. The next finding would be cell M6 of 1.0035, and it would match to cell J19, thus indicating that a value of 151 would be inserted into cell F19. Once that occurs, cell F3 should equate to 69,759 since the original volume is subtracting 90 and 151 from 70,000. Once cell F3 reaches 0, then I would try to get the loop to stop.

Please let me know if you have any guidance on how to approach this as I'm completely stumped. I can find the minimum value and paste in the ranking, but that's about as far as I've gotten. Any insight would be greatly appreciated.

Thank you,
Kniggit88

This is much appreciated, thank you. I've never utilized the LET function so I will have to look into that. Thank you for teaching me something new and I appreciate you adding the helper column in P as well.