Function Problems

How to Dynamically Select Month-Based Columns in Excel with CHOOSECOLS

Elise WilliamsElise Williams Nov 13, 2026 868 views

Question details

The user needs to replace hard-coded month column references in a Microsoft 365 FILTER and CHOOSECOLS formula with a dynamic column offset based on the selected month or a month header.

How to Dynamically Select Month-Based Columns in Excel with CHOOSECOLS
Product
WPS Spreadsheet
Device & OS
not provided
Scenario
The user has a monthly data table and wants the report formula to automatically follow the selected month and the next three columns without manually changing column references.
Observed behavior
The current formula uses hard-coded month column references, so the output does not update automatically when the selected month changes.
Before you start

Confirm that your WPS Spreadsheet version supports dynamic array functions such as LET, XMATCH, FILTER, and CHOOSECOLS, and that the month header row uses the same format as the month selector cell.

Solution 1Recommended

Use LET, XMATCH, and CHOOSECOLS to create a dynamic month offset

Use this method when the report must always return the selected month and the following three columns, without editing the month column letters or numbers manually.

The formula uses XMATCH to locate the selected month in the header row, stores the current and next three month columns with CHOOSECOLS, and then passes that dynamic range into the FILTER criteria. This keeps the output columns synchronized with the month selector.

1
Select the report cell

Open WPS Spreadsheet, go to the worksheet that contains the report formula, and click the cell where the dynamic result should appear.

2
Enter the dynamic formula

Type or paste the following formula and replace the ranges as needed: =LET(start,XMATCH($A$2,Data!G4:T4),monthData,CHOOSECOLS(Data!G5:T144,start,start+1,start+2,start+3),IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(monthData,1)<40000)*(CHOOSECOLS(monthData,2)<40000)*(CHOOSECOLS(monthData,3)<40000)*(CHOOSECOLS(monthData,4)<40000),""),1,4,5,8,9,10,11),"No accounts"))

3
Point the formula to your month selector and headers

Replace $A$2 with the cell that contains the selected month and replace Data!G4:T4 with the actual header range. Make sure the header values exactly match the selector cell format.

4
Verify the dynamic output

Press Enter, then change the month selector cell. Confirm that the formula returns the selected month and the following three columns automatically, or shows "No accounts" when no rows match.

Use LET, XMATCH, and CHOOSECOLS to create a dynamic month offset
Formula returns dynamic month columns: The report now updates from the month selector instead of relying on hard-coded month column references.
WPS Spreadsheet dynamic formula

Create a Dynamic Month-Column Report in WPS Spreadsheet

Use WPS Spreadsheet's LET, XMATCH, FILTER, and CHOOSECOLS functions to build a report that automatically follows the selected month and the next three columns without manual column editing.

  1. 1. Open the workbook: Launch WPS Spreadsheet and open the Excel workbook that contains the monthly data table and report formula.
  2. 2. Select the report cell: Click the cell where the dynamic FILTER and CHOOSECOLS result should be displayed.
  3. 3. Enter the dynamic month formula: Enter the LET, XMATCH, CHOOSECOLS, and FILTER formula, using the month selector cell as the start reference for the column offset.
  4. 4. Adjust ranges and output columns: Update the header range, data range, and output column positions such as 1,4,5,8,9,10,11 to match your report layout.
  5. 5. Test with a different month: Change the month selector cell and confirm that the result moves to the selected month and the next three columns.
Eliminates hard-coded month column references for automatic report updates.Uses familiar dynamic array functions with full .xlsx file compatibility.Free and lightweight spreadsheet solution for dynamic monthly reports.Preserves Microsoft Excel formulas and workbook structure when opened in WPS Spreadsheet.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use XMATCH instead of hard-coded column numbers?

XMATCH finds the selected month in the header row and returns its relative position. That position is then used by CHOOSECOLS to reference the current month and the following three columns, making the formula update automatically when the selector changes.

Will this formula work in WPS Spreadsheet if the workbook was created in Microsoft 365?

Yes, as long as your WPS Spreadsheet version supports dynamic array functions such as XMATCH, CHOOSECOLS, FILTER, and LET. Open the .xlsx workbook and check that the header and data ranges are correct for your file.

What should I check if the formula returns #N/A or #VALUE!?

Verify that the selected month cell and the month header row use the same text or date format. Also make sure the start value returned by XMATCH is at least 1 and that adding 3 does not exceed the number of columns in the month range.

How can I return more or fewer than three following month columns?

Change the start, start+1, start+2, start+3 arguments inside CHOOSECOLS and update the FILTER criteria to include the same number of month column checks, then adjust the final CHOOSECOLS output positions as needed.