How to List Matching Store Names in One Excel Cell
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).

- 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.
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.
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.
Click on the cell where you want the combined list of store names to appear (for example, H2).
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.
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.

Redesign Database and Use a Pivot Table
For a more robust and scalable solution, restructure your data into a normalized format and use Pivot Tables with slicers.
Create a Custom VBA Function
Use a macro if you are using older versions of Excel that do not support the TEXTJOIN function.
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. Open your workbook: Launch WPS Spreadsheet and open your existing file containing the data matrix.
- 2. Input the formula: Select your destination cell and type the `=TEXTJOIN()` formula nested with your `IF` condition.
- 3. View the combined list: Press Enter to instantly extract and combine the matching store headers.
- 4. Fill down: Drag the fill handle at the bottom-right of the cell to apply the formula across your entire dataset.

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.




