Function Problems

How to Use TEXTJOIN to List Unique Headers with Specific Values

Huda QurayshiHuda Qurayshi Oct 10, 2026 873 views

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.

How to Use TEXTJOIN to List Unique Headers with Specific Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell where you want the final, comma-separated list of organizations to appear.

2
Input the dynamic array formula

Enter the following formula: =LET(headers,B1:D1,data,B2:D4,hasYes,BYCOL(data,LAMBDA(c,COUNTIF(c,"yes"))),TEXTJOIN(", ",TRUE,IF(hasYes,headers,"")))

3
Adjust your data ranges

Modify B1:D1 to match the row containing your organization headers, and B2:D4 to match the actual data grid containing the "yes" values.

4
Execute the formula

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.

Use a Column-Based Array Formula with LET and BYCOL
Formula Logic: The BYCOL function creates an array of column counts. If the count is greater than 0, the IF function returns the header. TEXTJOIN then merges these headers while ignoring any empty strings.

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your organization data.
  2. 2. Apply the dynamic array formula: Select your output cell and paste the LET and TEXTJOIN combination formula.
  3. 3. Get instant results: Press Enter to instantly generate your duplicate-free, comma-separated list.
Fully compatible with Microsoft Excel formulas and dynamic arrays.Process complex data models quickly with lightweight, optimized performance.Includes a vast library of built-in functions for seamless data analysis.
microsoft office alternative - wps office

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.