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:

intended result

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

1 Answer 1


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
    Commented 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
    Commented 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. Commented Mar 6, 2014 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.