How can I find the best many-to-many invoice and payment matches without the result depending on row order?

Question

Hi, I’m exploring a more complex invoice-reconciliation scenario in Excel as a learning exercise, and I’m trying to understand the correct approach.

I have two tables.

Invoices

Invoice ID	Customer ID	Currency	Invoice Date	Due Date	Amount
I001	C01	USD	2026-01-01	2026-01-10	100
--------	--------	--------	--------	--------	--------
I001	C01	USD	2026-01-01	2026-01-10	100
I002	C01	USD	2026-01-20	2026-02-10	100

Payments

Payment ID	Customer ID	Currency	Payment Date	Amount
P001	C01	USD	2026-01-25	100
--------	--------	--------	--------	--------
P001	C01	USD	2026-01-25	100
P002	C01	USD	2026-02-20	100

One payment may cover several invoices, and one invoice may also be paid using several payments.

A match is valid only when:

• The customer is the same.

The currency is the same.

The payment date falls between the invoice date and 30 days after the invoice due date.

Split allocations are allowed, but an invoice or payment cannot be allocated beyond its remaining balance. A difference of up to 0.01 is acceptable when determining whether an invoice has been fully reconciled.

My concern is that a row-by-row matching approach may produce different results depending on how the tables are sorted.

In the example above, P001 is valid for either invoice, but P002 is valid only for I002 because of the date rule. Therefore, the correct allocation is:

Payment ID	Invoice ID	Allocated Amount
P001	I001	100
--------	--------	--------
P001	I001	100
P002	I002	100

A first-match approach could assign P001 to I002 and leave I001 unmatched, even though a complete reconciliation exists.

I would like the matching process to apply the following priorities in this order:

Minimize the total unmatched invoice and payment balances.

Minimize the number of split allocations.

Prefer matches with the smallest difference between the payment date and the invoice due date.

If more than one equally good solution remains, use the lowest Invoice ID and Payment ID as the final tie-breakers.

The process should produce an auditable allocation table with the following structure:

|Match ID|Invoice ID|Payment ID|Allocated Amount|Remaining Invoice Balance|Remaining Payment Balance| | -------- | -------- | -------- | -------- | -------- | -------- | It should also identify customer and currency groups for which no complete reconciliation is possible.

I’m not necessarily expecting this to be solved using a single worksheet formula. Would Excel Solver, Power Query, Python in Excel, or a combination of these tools be appropriate?

How could I verify that the final result is globally optimal rather than simply being the first valid combination found?

I would also like to understand whether this approach could realistically scale to approximately 50,000 invoices and 30,000 payments, assuming the data is first divided into smaller customer and currency groups.Hi, I’m exploring a more complex invoice-reconciliation scenario in Excel as a learning exercise, and I’m trying to understand the correct approach.

I have two tables.

Invoices

Invoice ID	Customer ID	Currency	Invoice Date	Due Date	Amount
I001	C01	USD	2026-01-01	2026-01-10	100
I002	C01	USD	2026-01-20	2026-02-10	100

Payments

Payment ID	Customer ID	Currency	Payment Date	Amount
P001	C01	USD	2026-01-25	100
P002	C01	USD	2026-02-20	100

One payment may cover several invoices, and one invoice may also be paid using several payments.

A match is valid only when:

The customer is the same.

The currency is the same.

The payment date falls between the invoice date and 30 days after the invoice due date.

Split allocations are allowed, but an invoice or payment cannot be allocated beyond its remaining balance. A difference of up to 0.01 is acceptable when determining whether an invoice has been fully reconciled.

My concern is that a row-by-row matching approach may produce different results depending on how the tables are sorted.

In the example above, P001 is valid for either invoice, but P002 is valid only for I002 because of the date rule. Therefore, the correct allocation is:

Payment ID	Invoice ID	Allocated Amount
P001	I001	100
P002	I002	100

A first-match approach could assign P001 to I002 and leave I001 unmatched, even though a complete reconciliation exists.

I would like the matching process to apply the following priorities in this order:

Minimize the total unmatched invoice and payment balances.

Minimize the number of split allocations.

Prefer matches with the smallest difference between the payment date and the invoice due date.

If more than one equally good solution remains, use the lowest Invoice ID and Payment ID as the final tie-breakers.

The process should produce an auditable allocation table with the following structure:

|Match ID|Invoice ID|Payment ID|Allocated Amount|Remaining Invoice Balance|Remaining Payment Balance| | -------- | -------- | -------- | -------- | -------- | -------- | It should also identify customer and currency groups for which no complete reconciliation is possible.

I’m not necessarily expecting this to be solved using a single worksheet formula. Would Excel Solver, Power Query, Python in Excel, or a combination of these tools be appropriate?

How could I verify that the final result is globally optimal rather than simply being the first valid combination found?

I would also like to understand whether this approach could realistically scale to approximately 50,000 invoices and 30,000 payments, assuming the data is first divided into smaller customer and currency groups.

Answer 1

Dear @Sirri Berat Cinkilic,

For you to be assisted properly, please reach out to Excel | Microsoft Community Hub. The reason I am asking you to post a new discussion is that in the Questions – Microsoft Q&A, we have limited resources and very few resources to test this feature and in Excel | Microsoft Community Hub, you will get the most qualified group of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction. 

Thank you for your cooperation and understanding. Feel free to post your questions in the Microsoft community and we'll always do our best to help you! 

Have a good day! 

Answer 2

For this type of reconciliation problem, a combination of Python in Excel (or Power Query for data preparation) with an optimization algorithm is the most appropriate approach. Excel Solver may work for small datasets, but it is unlikely to scale efficiently to tens of thousands of invoices and payments.

To ensure the result is globally optimal rather than the first valid match found, the allocation should be formulated as an optimization problem with an objective function that minimizes split allocations, minimizes the difference between payment and due dates, and applies Invoice ID and Payment ID as tie-breakers. Comparing the objective value with the optimizer's final solution and validating constraints provides confidence that the solution is globally optimal.

For datasets of approximately 50,000 invoices and 30,000 payments, performance becomes practical only after dividing the data into independent customer and currency groups, allowing each group to be solved separately.