Referencing what cells add up to a value

[J_Niggebrugge]

I was hoping to get some help on if there is a formula out there that has ability to help reconcile a specific value. I have a list of invoices and their respective dollar values. If a vendor lumps multiple invoices together into a check, is there a formula to help determine what invoices were lumped into that check? This will avoid having to try multiple combinations to see what invoices add up to the check value. I have included a very simpled down example; in my spreadsheet I have a lot more rows.

Invoice	$ Amt
A	$ 25.12
B	$ 62.50
C	$ 38.08
D	$ 33.93
E	$ 21.00
F	$ 410.00
G	$ 66.55
H	$ 555.00
I	$ 12.00
J	$ 12.42

<tbody>
</tbody>

If I have received a check for $539.15, is there a way I can have excel tell me that the combination of Invoice B, F, & G would equal $539.15?

Thank you!

 

[AliGW]

Even if there were, what if there were more than one combination of invoices that added up to that amount? How would you determine which were the right ones?

 

[Oaktree]

Hello and welcome to the board!

Try using Solver as in the example outlined here: http://www.mrexcel.com/forum/excel-questions/49138-looking-specific-sum-within-range-numbers.html

If, as in AliGW's question, there are multiple combinations that could give the result, you might have a look at Tushar's code here: http://www.tushar-mehta.com/excel/templates/match_values/index.html

 

[J_Niggebrugge]

If there are multiple combinations then it would have to be analyzed further, but alot of the check values I work with are very unique and large values (usually comprised of 1 or 2 invoices). If nothing else, it is just a starting point that could save a lot of time and it could avoid having to go back to the vendor to figure it out.

Thanks,