Function Problems

How to Find the Largest Matching Sum Between Two Excel Lists

Bushra Parveen Sep 30, 2026 869 views

Question details

The user needs to find combinations of numbers from two different lists that result in the highest possible equal sum.

How to Find the Largest Matching Sum Between Two Excel Lists

Product: Excel
Device & OS: not provided
Scenario: Reconciling two large lists of financial values (such as assigning payments to invoices) to maximize the amount eliminated from both lists.
Observed behavior: Finding the highest matching total is computationally difficult because the number of possible subset combinations for hundreds of values is enormous.

Before you start

Ensure both lists are cleaned of any formatting errors or empty cells, and enable the Solver Add-in in your spreadsheet software to handle complex mathematical optimization tasks.

Solution 1Recommended

Use the Solver Add-in to Maximize Matching Sums

The Solver tool uses mathematical optimization to test multiple combinations of variables, making it ideal for structured subset-sum problems.

Since this is a subset-sum optimization problem, the number of possible combinations can be enormous for lists with hundreds of rows. A structured optimization model using binary variables is the most effective approach to identify exact matching sums.

Set up binary indicator columns

Insert a new column next to both of your financial lists. Fill these new indicator columns entirely with 0s. These will act as binary triggers (0 for excluded, 1 for included) for the Solver.

Create SUMPRODUCT formulas

In an empty cell, write a =SUMPRODUCT() formula that multiplies your first financial list by its corresponding indicator column. Do the same for the second list in another cell.

Calculate the difference

Create a 'Difference' cell that subtracts the second SUMPRODUCT result from the first SUMPRODUCT result. The goal is for this difference to be exactly 0.

Configure the Solver parameters

Navigate to the Data tab and click Solver. Set your Objective to maximize the first SUMPRODUCT cell. Set the 'By Changing Variable Cells' to both of your binary indicator columns.

Add binary constraints and solve

Add a constraint that the 'Difference' cell must equal 0, and another constraint that forces the indicator variable cells to be 'bin' (binary). Click Solve to let the application compute the maximum matching sum.

Use the Solver Add-in to Maximize Matching Sums

Processing Time: Depending on the processing power of your PC and the size of your lists, the Evolutionary or GRG Nonlinear solver methods may take several minutes to compute the optimal result.

Solution 2

Use Random Sampling for Extremely Large Lists

If the dataset is too large for the Solver to process in a reasonable time, you can use randomized formulas to sample subsets and find a sufficiently high matching sum by inspection.

Efficient Data Analysis with WPS Office

Solve Complex Data Matching with WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis tools, including built-in What-If Analysis and Solver capabilities, to help you tackle computationally heavy financial reconciliation tasks with ease.

1. Open your financial dataset: Launch WPS Spreadsheet and open your .xlsx file containing the lists of values to be matched.
2. Access data tools: Navigate to the Data tab on the top ribbon and locate the What-If Analysis or Solver tool.
3. Set your optimization objective: Define your objective cell to maximize the matched sum, and select your variable ranges.
4. Apply binary constraints: Add rules to ensure your selection variables are strictly 0 or 1, and the difference between the two lists equals zero.
5. Run the computation: Click Solve to let WPS Spreadsheet compute the optimal combination of values for your reconciliation.

Built-in What-If Analysis and optimization tools to solve subset-sum problemsFully compatible with Microsoft Excel .xlsx and .xls formatsFamiliar interface for seamless migration without a learning curveFree and lightweight alternative for heavy computational tasks

microsoft office alternative - wps office

Frequently Asked Questions

What is a subset-sum problem in spreadsheets?

A subset-sum problem is a mathematical optimization challenge where the goal is to find a specific combination (subset) of numbers in a larger list that adds up to a target value. In financial scenarios, it's often used to match multiple smaller payments to a batch of outstanding invoices.

Why does my spreadsheet freeze when trying to match large lists?

As the number of items in your lists increases, the number of possible mathematical combinations grows exponentially. Comparing hundreds of values simultaneously requires massive computational power, which can cause spreadsheet software on standard PCs to slow down or become unresponsive.

How do I enable the Solver Add-in?

In most modern spreadsheet software, including Excel, you can enable Solver by going to File > Options > Add-ins. At the bottom, select Manage Excel Add-ins, click Go, check the box next to Solver Add-in, and click OK. The tool will then appear in your Data tab.

Is there a formula to automatically find matching sums?

There is no single built-in formula that can automatically find optimal subset combinations because it requires iterative calculations. You must rely on advanced tools like Solver, VBA macros, or randomized heuristic sampling methods to find matching values.

Excel & Data Problems