CODEWITHSUNDEEP
google-sheetsformula
Björn Andersson
Björn Andersson

Reputation: 13

Google sheet formula that finds row name and returns column headers based on marked cells

I'm trying to return the column headers for a row that is marked with an x. The row is selected from a name in the left column. I'm stuck here.

I can illustrate what I want to do by showing these images: Start table

The result I want is this: Outputs of the possibilities for the first sheet

I have put more information in my Example Sheet.

Link to editable example sheet

Upvotes: 0

Views: 235

Answers (1)

JPV
JPV

Reputation: 27262

This formula should create a table (with a single formula) with the months in one column and the headers in the second column.

=ArrayFormula({A4:A15\ substitute(transpose(query(transpose(if(B4:G15="x";B3:G3&char(10);));;rows(A4:A15)));" ";)})

If you'd want to 'lookup' the months you manually type in you can wrap the above in a vlookup. Example:

=ArrayFormula(if(len(L4:L); vlookup(L4:L; {A4:A15\ substitute(transpose(query(transpose(if(B4:G15="x";B3:G3&char(10);));;rows(A4:A15)));" ";)}; 2; 0);))

You can check out both formulas in the copy of the sheet I've made in the spreadsheet you shared.

Upvotes: 2

Related Questions