How to Find the Best Column Combination Below a Target in Excel with VBA
Question details
The user needs to select one value from each column in a table so the total is as high as possible but does not exceed a target, using Excel-compatible VBA or another optimization method.

- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a data range with multiple columns and a target value, and wants to identify the best combination of one value per column.
- Observed behavior
- A standard worksheet formula cannot directly return the optimal combination; a recursive VBA function or a query/Solver workflow is required.
Before you start, save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and make sure the Developer tab or VBA editor is available so you can insert and run the recursive procedure.
Use a recursive VBA function to find the closest combination under a target
Best for small to medium ranges where you want a formula-driven result and can enable macros.
The recursive function checks one value from each column, totals the selection, and keeps the largest total that does not exceed the target. This avoids manually evaluating every combination.
In WPS Spreadsheet or Excel, press Alt+F11, or go to Developer > Visual Basic to open the VBA editor.
In the Project pane, right-click your workbook name, choose Insert > Module, and open the new module.
Paste a recursive FindSolution function that starts at the first column, selects each cell in turn, moves to the next column, and compares totals against the target cell.
Enter =FindSolution(A2:D11,G2) in the cell where you want the result, replacing A2:D11 with your data range and G2 with the target cell.
Generate and filter combinations with Power Query
Use this for larger datasets or when you prefer a query-based approach instead of writing VBA code.
Find the closest column combination under a target in WPS Spreadsheet
WPS Spreadsheet can handle this optimization with a recursive VBA function in a macro-enabled workbook, or with Power Query for larger datasets, while keeping full compatibility with Excel files.
- 1. Save as a macro-enabled workbook: Open the file in WPS Spreadsheet and save it as an Excel Macro-Enabled Workbook (.xlsm).
- 2. Open the VBA editor: Press Alt+F11 or click Developer > Visual Basic to open the VBA editor.
- 3. Insert the FindSolution module: Right-click the project in the Project Explorer, choose Insert > Module, and paste the recursive VBA function.
- 4. Return to the worksheet and run the formula: In a cell, enter =FindSolution(A2:D11,G2), using your actual data range and target cell.
- 5. Enable macros if prompted: Click Enable Content when the macro security warning appears so the custom function can calculate.

Frequently Asked Questions
Does this method work in WPS Spreadsheet?
Yes. Save the workbook as .xlsm, enable macros, and insert the VBA function in a standard module, then call it from a worksheet cell.
What formula should I use to return the best total?
Use =FindSolution(A2:D11,G2), replacing A2:D11 with the range containing your columns and G2 with the target cell.
Why does my function return an error or zero?
Usually the workbook is not saved as .xlsm, macros are disabled, or the function is placed in a worksheet module instead of a standard module.
What can I do if the dataset is too large for recursion?
Use Power Query to generate and filter combinations, or try Solver or another optimization approach to avoid evaluating every combination.
Can I show which value was selected from each column?
Yes. Modify the VBA procedure to store or output the selected cell addresses and values when it finds a better valid total.




