Looking up the closest sum of two variables and displaying solution

[Jason2345]

I have attached a color coded example sheet to help explain this situation.


I am trying to solve an issue where measurements are taken based on the percentage capacity of a device (blue cells). I need excel to look at the green options cells to come up with the closest match possible by summing two of the options together if needed. I would like to display the values of the options used in the yellow and orange cells while also displaying the sum of the two in the red cells.

I was thinking the easiest way would be to produce an options list that shows all possible solutions using the sum of two available values and then conditionally formatting those cells that the solutions reside in to red or bold or whatever. Is it possible to get excel to recognize the cells meeting the conditional formatting rules and display the values of those cells in another cell?

There are some rules that have to be followed.

1) The total cannot be less than the smallest option
2) the total cannot be larger than the capacity
3) Options cannot be used more than once

Hopefully I have provided enough information to help with a solution.

Thank you in advance,

Jason