How to Return Multiple Matching Values as a Comma-Separated List in Excel
Question details
The user wants to return every matching column heading in one cell as a comma-separated list based on a specific cell value condition.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating multiple matching column headers into a single cell, separated by commas, when their corresponding data cells meet a specific criteria (e.g., containing 'Yes').
- Observed behavior
- The user needs an efficient formula to lookup multiple matching values horizontally or vertically and combine them into one comma-separated text string.
Verify that your version of Excel supports dynamic array functions like FILTER and TEXTJOIN (Office 365, Excel 2019 or newer), or prepare to use the alternative array formula for older versions.
Use TEXTJOIN and FILTER (Recommended for Newer Versions)
Combine the TEXTJOIN and FILTER functions to easily extract and join matching column headings in modern Excel versions.
The FILTER function is used to extract the column headers that meet your criteria, while TEXTJOIN consolidates those extracted headers into a single text string separated by a comma.
Click on the cell (e.g., B2) where you want the comma-separated list of matching values to appear.
Type the formula: =TEXTJOIN(", ",TRUE,FILTER($C$1:$L$1,C2:L2="Yes","")). Here, $C$1:$L$1 is your header row, and C2:L2 is the row containing the data to check against "Yes".
Press Enter to generate the list. You can then drag the fill handle down to apply this formula to other rows in your dataset.
Use TEXTJOIN and IF (For Older Excel Versions)
If your version of Excel does not support the FILTER function, you can use the IF function nested inside TEXTJOIN as an array formula.
Use WPS Spreadsheet to Handle Complex Formulas Easily
WPS Spreadsheet fully supports modern dynamic array functions like FILTER and TEXTJOIN, making it simple to return multiple matching values as a comma-separated list. Enjoy a seamless, highly compatible spreadsheet experience.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Select the output cell: Click the cell where you want the comma-separated list to be generated.
- 3. Enter the formula: Input the TEXTJOIN and FILTER formula just as you would in Microsoft Excel.
- 4. Press Enter: Hit Enter to instantly calculate and display the combined matching values.
Frequently Asked Questions
Why is the FILTER function returning a #NAME? error?
The #NAME? error usually occurs if your version of the spreadsheet software does not support the FILTER function. Ensure you are using a modern version like Office 365, Excel 2021, or the latest version of WPS Office. For older versions, use the IF array formula alternative.
How can I change the separator in the TEXTJOIN formula?
The first argument in the TEXTJOIN function defines the delimiter. To change it from a comma and space (", ") to a dash or a slash, simply update the first argument inside the quotation marks, like this: =TEXTJOIN(" - ", TRUE, ...).
What does the 'TRUE' argument do in the TEXTJOIN function?
The 'TRUE' argument tells the TEXTJOIN function to ignore empty cells. If a cell matches your criteria but contains no text, it will be skipped, preventing your final list from having consecutive commas (e.g., 'Item1, , Item3').
