Sometimes we are in a situation where we have a target figure plus a series of values and we want to know which, if any, combination of those values has a sum which is equal to the target.
This can be done as follows:
Edit: this post has now been revised here to account for multiple returns, should that be a requirement.
Using the above set-up, with our target value in A2 and our (in this case 9) values in C1:K1, we will place formulas in C2:K2 which will contain an “X” if the value in the row above forms part of our solution.
In the example given, you can easily verify that 10+20+12+5 does indeed equal 47.
First, as I often like to do, we will define the range of values, C1:K1, as Range1 using Name Manager (Formulas tab). This solution will work for other ranges as well, of more or less than 9 cells, though readers should note that this solution is designed to work with a horizontal range of values: for the equivalent set-up using a vertical range, see the comments at the end of this post.
We then make two further additions to Name Manager, viz:
The array formula in C2 is then:
Copy across as required.
1) This set-up has a theoretical limit on the size of the dataset of 20 values. However, it is more than likely that Excel will run out of resources in that case due to the sheer size of the calculations involved. I ran it with a dataset comprising 15 values, i.e. C2:Q2, and it just about coped, taking 5 seconds or so to calculate.
However, I would imagine that any more than that and Excel may begin to struggle, and so readers hoping for a solution with a dataset of such a size should probably seek a VBA-based solution instead
2) If no combination of the values exists for which the sum is equal to the target value then all cells remain blank.
3) If more than one combination of values has a sum equal to the target value then one of those combinations is given (the actual reasoning behind which one is chosen is relatively complex, though again, if anyone is interested I would be happy to expound.)