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.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Invoice[/TD]
[TD]$ Amt[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD]$ 25.12[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD]$ 62.50[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD]$ 38.08[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD]$ 33.93[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD]$ 21.00[/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD]$ 410.00[/TD]
[/TR]
[TR]
[TD="align: center"]G[/TD]
[TD]$ 66.55[/TD]
[/TR]
[TR]
[TD="align: center"]H[/TD]
[TD]$ 555.00[/TD]
[/TR]
[TR]
[TD="align: center"]I[/TD]
[TD]$ 12.00[/TD]
[/TR]
[TR]
[TD="align: center"]J[/TD]
[TD]$ 12.42[/TD]
[/TR]
</tbody>[/TABLE]

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,