I'm wondering if there's a formula I can use in the following situation.

I have a spreadsheet on Google Sheets with two data sets. The first data set shows the rows down as Items for example, and the Columns across as Categories. Each item has 3 ticks across the different categories (there are over 30 categories). The second data set shows the items again (in the same format), and instead of showing all the categories with the ticks, it should show which categories were ticked for each item.

Here's a screenshot of what that might look like.

E.g. Item 1 has a tick under categories 'A', 'C' and 'E'. I want to show another table/data set that shows the category header that Item 1 has ticks across. So in cells J5 - L5, 'A', 'C', 'E'. Because my data set has many items and categories, it's difficult to do this manually, so I'm wondering if there's a formula I can use to give me the column header values whereby if they are ticked for that particular item, the corresponding header value shows up in a separate data set next to it - this makes it easier for me to see which values are ticked rather than scrolling across to see them across 30 or so categories.

here's the sample image

1 Answer 1


For example from your screenshot just put to J5 =FILTER($C$4:$I$4,C5:I5<>"") and fill down


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.