# Merge column headings into one cell based on a specific value

I am relatively new to Google Spreadsheets. I would like to use spreadsheets to better analyze classroom data.
I am trying to write a formula to merge column headings into one cell based on a specific value. In the example below, I would like every column heading that has a `0` for a particular student to be written in one cell:

My goal is this: I've been searching through forums without much luck. Any advice to get me started would be much appreciated.

Try this formula for cell `H2`:

``````=JOIN(", ", FILTER(B\$1:E\$1, B2:E2 = 0))
``````

This filters the header row (`B1:E1`) on values from `B2:E2` which has the value `0`. The resulting strings are joined with `,`.

You can copy this formula to the other cells in column `H` by dragging it across. The `B2:E2` will adjust automatically to match the other rows.

Feel free to look at and copy the example spreadsheet I've set up.

• Worked perfectly. Thank you very much!
– Josh
Mar 6, 2014 at 18:20
• Will do. I was just playing around with your formula in some other spreadsheets I created. Is it possible to do a quick add on to the formula so that for students that did not get any words wrong (i.e. they have all 1s) that cell will read something like "all correct" instead of #N/A? Thanks again. That's a big help.
– Josh
Mar 6, 2014 at 18:33
• Yes, something like `=IF(SUM(B2:E2) = 4, "All correct", JOIN(", ", FILTER(B\$1:E\$1, B2:E2 = 0)))`. So if the total score for the student is `4`, display `All correct`, else display the problematic words. Mar 6, 2014 at 18:45