How to Return All Column Headers for Cells Containing 1 in Excel
Question details
The user needs to retrieve and combine all column headers where the corresponding cell in a row contains the value 1, bypassing the single-match limitation of standard lookup formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting multiple corresponding column headers for specific cell values across a dataset to categorize or summarize row data.
- Observed behavior
- Standard lookup functions like INDEX and MATCH only return the first matching column header, rather than returning every header where the criteria is met.
Determine whether you are using a modern version of Excel (such as Microsoft 365 or Office 2021) that supports Dynamic Arrays and the FILTER function, or an older legacy version, as the optimal formula will differ significantly.
Use TEXTJOIN and FILTER in Modern Excel
Combine the TEXTJOIN and FILTER functions to easily return all matching column headers separated by a comma in a single cell. This is the most efficient method for modern Excel versions.
The FILTER function can extract an array of headers where the corresponding row values equal 1. By wrapping it in TEXTJOIN, you merge these results into a neat, comma-separated text string.
Click on the empty cell where you want the combined column headers to appear.
Type the formula: =TEXTJOIN(", ", TRUE, FILTER($A$1:$D$1, A2:D2=1, "")). Replace $A$1:$D$1 with your actual absolute header range, and A2:D2 with the relative row data you are evaluating.
Press Enter. The cell will display all matching column headers. You can click and drag the fill handle down to apply this formula to the remaining rows in your dataset.
Use TEXTJOIN and IF as an Array Formula (Excel 2019)
If your Excel version supports TEXTJOIN but lacks the FILTER function (like Excel 2019), you can use an IF array formula to achieve the same concatenated result.
Use INDEX, SMALL, and IF for Legacy Excel Versions
For older Excel versions lacking TEXTJOIN, use a combination of INDEX, SMALL, IFERROR, and IF to extract matches into separate adjacent columns.
Effortlessly Extract Multiple Headers with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic arrays and modern functions like TEXTJOIN and FILTER. You can seamlessly process complex lookups and instantly return multiple matching values without struggling with complicated legacy array formulas.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your dataset containing the headers and row values.
- 2. Select the output cell: Click the blank cell where you want the matching headers to be combined.
- 3. Apply the modern formula: Type =TEXTJOIN(", ", TRUE, FILTER($A$1:$D$1, A2:D2=1, "")) into the formula bar.
- 4. Press Enter: Hit Enter on your keyboard. WPS Spreadsheet will instantly process the dynamic array and display all headers that meet the criteria.
Frequently Asked Questions
Why does my INDEX and MATCH formula only return the first header?
The standard MATCH function is designed to stop searching as soon as it finds the very first value that meets your criteria. To return multiple matches, you must use array formulas or modern dynamic array functions like FILTER.
What does the #NAME? error mean when using FILTER or TEXTJOIN?
The #NAME? error indicates that the spreadsheet software does not recognize the function. This usually happens if you are using an older version of Excel (like Excel 2016 or earlier) that does not have TEXTJOIN or FILTER built into it. Consider upgrading or using a free modern alternative like WPS Office.
How do I return the headers if the cell contains specific text instead of the number 1?
You can easily modify the criteria in the formula. For example, if you want to find the text 'Yes', change A2:D2=1 to A2:D2="Yes" inside the FILTER or IF function.
Do I need to press Ctrl+Shift+Enter when using modern functions?
No, if you are using Microsoft 365, Excel 2021, or WPS Spreadsheet, dynamic arrays are natively supported. You only need to press Enter. Ctrl+Shift+Enter is only required for legacy array formulas in older versions.
