CODEWITHSUNDEEP
excelgoogle-sheetsautomationgoogle-sheets-formulaindex-match
gogilan
gogilan

Reputation: 163

select all columns that contain specifc value in excel / googlesheets

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:

enter image description here

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.

enter image description here

Upvotes: 1

Views: 75

Answers (2)

player0
player0

Reputation: 1

try just:

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

enter image description here

Upvotes: 2

rockinfreakshow
rockinfreakshow

Reputation: 29904

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

-

enter image description here

Upvotes: 1

Related Questions