Function Problems

How to List Matching Store Names in One Excel Cell

John WilsonJohn Wilson Oct 29, 2026 871 views

Question details

The user needs to return multiple matching store names (column headers) into a single cell for each row based on binary cell values (1 or 0).

How to List Matching Store Names in One Excel Cell
Product
Excel
Device & OS
not provided
Scenario
Generating a comma-separated list of stores that carry a specific item based on a matrix of 1s and 0s.
Observed behavior
The goal is to automatically combine all column headers into one cell if the corresponding row contains a 1, avoiding manual data entry.
Before you start

Ensure your data is organized with clear column headers for the store names and that the matrix accurately uses 1 and 0 values to represent matches.

Solution 1Recommended

Use the TEXTJOIN and IF Functions

Combine matching column headers into a single cell using a dynamic array formula. This is the most efficient method for recent Excel versions.

The TEXTJOIN function allows you to combine text from multiple ranges with a specified delimiter, while ignoring empty cells. By nesting an IF function, you can dynamically extract the headers that meet your criteria.

1
Select the target cell

Click on the cell where you want the combined list of store names to appear (for example, H2).

2
Enter the TEXTJOIN formula

Input the formula `=TEXTJOIN(", ", TRUE, IF(C2:G2=1, $C$1:$G$1, ""))`. Adjust `C2:G2` to match your data row and `$C$1:$G$1` to match your column headers.

3
Apply the formula

Press Enter. If you are using Excel 2019, you must press Ctrl+Shift+Enter to confirm it as an array formula. Drag the fill handle down to apply the formula to the remaining rows.

Use the TEXTJOIN and IF Functions
Version Compatibility: The TEXTJOIN function is only supported in Microsoft 365, Excel 2019, and Excel 2021.
Efficient Data Management

Combine Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN and IF, making it incredibly easy to combine matching data into a single cell without writing complex VBA code.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open your existing file containing the data matrix.
  2. 2. Input the formula: Select your destination cell and type the `=TEXTJOIN()` formula nested with your `IF` condition.
  3. 3. View the combined list: Press Enter to instantly extract and combine the matching store headers.
  4. 4. Fill down: Drag the fill handle at the bottom-right of the cell to apply the formula across your entire dataset.
Fully compatible with Microsoft Excel formulas, including TEXTJOIN and IF.Lightweight software that processes large datasets quickly and without lag.Built-in Pivot Table features for robust and normalized data analysis.Free to download and use with a familiar, easy-to-navigate user interface.
microsoft office alternative - wps office

Frequently Asked Questions

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

The #NAME? error typically occurs if you are using an older version of Excel (such as Excel 2016 or earlier) that does not support the TEXTJOIN function. You will need to upgrade to a newer version or use a VBA workaround.

Can I use a delimiter other than a comma?

Yes, the first argument of the TEXTJOIN function is the delimiter. You can change ", " to any string you prefer, such as a hyphen "-" or a space.

Do I need to press Ctrl+Shift+Enter for this formula to work?

In Microsoft 365, Excel 2021, and modern versions of WPS Office, you only need to press Enter because they support dynamic arrays. In older versions like Excel 2019, you must press Ctrl+Shift+Enter to evaluate the nested IF function properly as an array formula.