How to Use TEXTJOIN to List Unique Headers with Specific Values
Question details
The user needs to list organization names (column headers) that contain at least one "Yes" in their respective columns, avoiding duplicate names and without using a helper row.

- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Extracting and combining unique column headers into a single cell based on a specific criteria met across multiple rows within those columns.
- Observed behavior
- Using standard array evaluations or MMULT-based formulas results in the TEXTJOIN function returning every matched organization name multiple times instead of a clean, deduplicated list.
Ensure your spreadsheet software is updated to a version that supports dynamic array functions, specifically LET, BYCOL, and LAMBDA, as these are required to process column-based logical checks.
Use a Column-Based Array Formula with LET and BYCOL
This solution uses BYCOL to count specific values per column and evaluates them as a single array, ensuring headers are only returned once to TEXTJOIN.
By wrapping the logic inside a LET function, you can define your header and data ranges clearly. The BYCOL function then passes each column into a LAMBDA function to count the target value, effectively avoiding the duplication issue caused by evaluating cell-by-cell.
Click on the cell where you want the final, comma-separated list of organizations to appear.
Enter the following formula: =LET(headers,B1:D1,data,B2:D4,hasYes,BYCOL(data,LAMBDA(c,COUNTIF(c,"yes"))),TEXTJOIN(", ",TRUE,IF(hasYes,headers,"")))
Modify B1:D1 to match the row containing your organization headers, and B2:D4 to match the actual data grid containing the "yes" values.
Press Enter. The formula will evaluate the columns, returning a clean list of headers with at least one "yes" separated by a comma and a space.

Process Advanced Array Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN, LET, and BYCOL. You can manipulate complex data conditionally without relying on messy helper rows or dealing with duplicated outputs.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your organization data.
- 2. Apply the dynamic array formula: Select your output cell and paste the LET and TEXTJOIN combination formula.
- 3. Get instant results: Press Enter to instantly generate your duplicate-free, comma-separated list.

Frequently Asked Questions
Why does my TEXTJOIN formula return duplicates when evaluating arrays?
Traditional array formulas (like those using MMULT) often evaluate data on a cell-by-cell basis rather than by column. If a column has three "Yes" values, the formula passes the header to TEXTJOIN three times. Using BYCOL ensures the logic evaluates the entire column as a single entity, preventing duplicates.
What if I want to check for a different text value instead of "Yes"?
You can easily modify the criteria within the COUNTIF section of the formula. For example, replace "yes" with "Completed" to make the formula =LET(headers,B1:D1,data,B2:D4,hasYes,BYCOL(data,LAMBDA(c,COUNTIF(c,"Completed"))),TEXTJOIN(", ",TRUE,IF(hasYes,headers,""))).
Can I change the delimiter used in the TEXTJOIN function?
Yes, the first argument in the TEXTJOIN function is the delimiter. You can change ", " to any character string you prefer, such as " - " for dashes, or use CHAR(10) to separate the organization names with line breaks.
Why am I getting a #NAME? error when using this formula?
A #NAME? error typically occurs if your spreadsheet software version does not support newer functions like LET, BYCOL, or LAMBDA. Ensure you are using the latest version of WPS Office or your preferred spreadsheet application to access these modern dynamic array functions.




