How to Dynamically Select Month-Based Columns in Excel with CHOOSECOLS
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.

- 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.
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.
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.
Open WPS Spreadsheet, go to the worksheet that contains the report formula, and click the cell where the dynamic result should appear.
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"))
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.
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.

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. Open the workbook: Launch WPS Spreadsheet and open the Excel workbook that contains the monthly data table and report formula.
- 2. Select the report cell: Click the cell where the dynamic FILTER and CHOOSECOLS result should be displayed.
- 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. 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. 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.

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.




