How to Return Multiple Column Headers for Duplicate Dates in WPS Office Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that returns every column header whose row value matches the target date in a final column, handling duplicate dates, varying column order, and blank cells.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a data range where multiple date columns may contain the same target date and wants a per-row list of all matching headers for comparison or reporting.
- Observed behavior
- Standard lookup functions return only the first matching header, so a dynamic array formula is required to collect all matches and ignore blanks.
Confirm that your WPS Office version supports dynamic array functions such as BYROW, FILTER, TAKE, and TEXTJOIN. Arrange your data so the header row is row 1 and the target date column is the final column in the range used by the formula.
Use BYROW, FILTER, TAKE, and TEXTJOIN to Return All Matching Headers
Use this dynamic array formula to spill all matching column headers for each row at once, while ignoring blank cells and working regardless of duplicate date order.
This formula treats the first 8 columns as the values to compare and the last column as the target date. FILTER returns only the headers where the row value equals the target, TEXTJOIN joins them with a comma, and IFERROR prevents errors when no match exists.
Click the first cell where the returned header list should appear, for example BG2.
Type or paste =IFERROR(BYROW(AX2:BF19,LAMBDA(row,TEXTJOIN(",",TRUE,FILTER($AX$1:$BE$1,TAKE(row,,8)=TAKE(row,,-1))))),"") into the formula bar.
Press Enter to confirm. WPS Spreadsheets spills the results down the selected range when dynamic array support is available.
Update AX2:BF19, $AX$1:$BE$1, and the TAKE column count 8 to match the actual value columns and target date column.
Use LET with a Match Matrix and TEXTJOIN for Flexible Header Output
Use this alternative if you prefer a more readable formula that first creates a header-or-blank matrix and then joins each row, or if you want to inspect the intermediate results.
Return Duplicate-Date Column Headers with a Dynamic WPS Spreadsheets Formula
WPS Spreadsheets supports Excel-compatible array functions, so you can build a FILTER, BYROW, TAKE, and TEXTJOIN formula that returns every matching column header for each row while ignoring blank cells and saving the workbook in .xlsx format.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook that contains the duplicate-date table.
- 2. Select the result cell and paste the formula: Click the first output cell and paste =IFERROR(BYROW(AX2:BF19,LAMBDA(row,TEXTJOIN(",",TRUE,FILTER($AX$1:$BE$1,TAKE(row,,8)=TAKE(row,,-1))))),"") into the formula bar.
- 3. Adjust the ranges and column count: Update AX2:BF19, $AX$1:$BE$1, and the number 8 in TAKE(row,,8) to match your actual value columns and target date column.
- 4. Press Enter and fill if needed: Press Enter to confirm the formula. WPS Spreadsheets spills results automatically; if your version does not spill, drag the fill handle down through the data rows.
- 5. Save the workbook: Click Menu > Save As and choose Excel Workbook (.xlsx) or WPS Spreadsheets (.et) to keep the formula.
Frequently Asked Questions
Why does the formula return an empty string in some rows?
IFERROR returns an empty string when the row has no matching header or when an error occurs. This is intentional so blank target cells and rows without matches stay clean instead of showing #N/A.
Can I use this formula if my headers are not in row 1?
Yes. Replace $AX$1:$BE$1 with the actual header range, and make sure the TAKE range and target column are positioned consistently. The header range must have the same number of columns as the value columns being compared.
How do I change the separator from a comma to a new line or semicolon?
Edit the first argument of TEXTJOIN in the formula. For example, use ";" for semicolons or CHAR(10) with wrap text enabled for line breaks.
Does WPS Spreadsheets support BYROW, TAKE, and FILTER?
Yes. WPS Office supports these dynamic array functions in current versions. If the formula returns #NAME?, update WPS Office or confirm your version supports dynamic arrays.
What should I do if the formula returns #SPILL!?
Clear any cells below or to the right of the formula output that may block the spill range, or reshape the data so the dynamic array result has empty space to expand.
