Function Problems

How to Return Matching Excel Column Headings in One Cell

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to list all column headers in a single cell if the corresponding cells in that row meet a specific condition, such as containing the letter 'Y'.

Product: Spreadsheets
Device & OS: not provided
Scenario: Creating a summary column that dynamically lists compatible radio models (column headers) based on compatibility indicators (Y) in each row.
Observed behavior: Requires a formula to evaluate a row's values against a criteria and combine the matching column headers into one comma-separated text string.

Before you start

Ensure your spreadsheet software supports dynamic array functions like FILTER and TEXTJOIN, which are available in modern versions of Microsoft Excel and WPS Office.

Solution 1Recommended

Use TEXTJOIN and FILTER Functions

Combine the FILTER function to extract matching column headers and the TEXTJOIN function to concatenate those headers into a single text string.

The FILTER function is ideal for returning an array of column headers where the row value matches your specific criteria. Wrapping this in a TEXTJOIN function allows you to compress the array into a single cell, separated by a delimiter of your choice.

Select the destination cell

Click on the cell where you want the combined list of column headers to appear (e.g., cell AX3).

Input the combination formula

Type the formula: =TEXTJOIN(", ",TRUE,FILTER($D$2:$AW$2,$D3:$AW3="Y","- None -")) into the formula bar.

Apply and fill down

Press Enter to display the comma-separated matching headers. Click the fill handle at the bottom-right of the cell and drag it down to apply the formula to the rest of the rows.

Formula Reference Breakdown: $D$2:$AW$2 represents the absolute range of your column headers. $D3:$AW3 is the current row being evaluated for the 'Y' value, which updates as you drag the formula down.

Work Smarter with WPS

Extract and Combine Spreadsheet Data Seamlessly in WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN and FILTER, allowing you to instantly match and combine column headings without needing complex workarounds.

1. Open your file in WPS: Launch WPS Spreadsheet and open your existing data table.
2. Enter the formula: Select your target cell and input =TEXTJOIN(", ",TRUE,FILTER($D$2:$AW$2,$D3:$AW3="Y","- None -")).
3. Apply across all rows: Double-click or drag the fill handle downwards to generate the matched column headings for every row in your dataset.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx)Natively supports modern dynamic arrays including TEXTJOIN and FILTERFree, lightweight, and processes large datasets quicklyFamiliar user interface for immediate adoption with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why is my TEXTJOIN formula returning a #NAME? error?

This error typically occurs if you are using an older version of Excel (like Excel 2013 or 2016) that does not support the TEXTJOIN or FILTER functions. To resolve this, update your software or use WPS Office, which natively supports these modern functions.

Can I use a different delimiter instead of a comma for the joined headers?

Yes. The first argument in the TEXTJOIN function sets the delimiter. You can change ", " to any text string, such as " - ", "/", or even CHAR(10) if you want each header to appear on a new line within the cell.

What happens if no cells in the row match the 'Y' criteria?

The third argument of the FILTER function determines the output when no matches are found. In the formula =TEXTJOIN(", ",TRUE,FILTER($D$2:$AW$2,$D3:$AW3="Y","- None -")), it will display '- None -'. You can easily change this string to 'No Match' or leave it blank as "".

Excel & Data Problems