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.

CodePudding user response：

try just:

=INDEX(QUERY(FLATTEN(IF(B11=B2:F10, B1:F1, )), "where Col1 is not null", ))

CodePudding user response：

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&"'"))

-