select all columns that contain specifc value in excel / googlesheets
Problem Description:
I have a table like this where Managers are listed in one row and all the months they are working are listed beneath them in the same column:
Now I want to create a quick way to determine based on the month, which managers are working. Right now I am trying to make it work such that when I click the month from a dropdown list, the managers should populate based on an index / match formula. However, I have been struggling to get it to work. Is there any way to do this in google sheets? I open to using scripts as well.
Solution – 1
the setup in the screenshot should be helpful in what the formula is aiming at:
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(B1:F1&"|"&B2:F5),"|"),"Select Col1 Where Col2='"&H1&"'"))
–
Solution – 2
try just:
=INDEX(QUERY(FLATTEN(IF(B11=B2:F10, B1:F1, )), "where Col1 is not null", ))
