Return multiple values from a single selection

[gruntingmonkey]

Not really sure how to phrase this question so apologies if I have not used the right terminology.

I'm looking to find out how to return multiple rows of information from a grid (can't be a table, this is a very small example of the data). Essentially, what is the formula to put in H4, H5 & H6 to bring up only Activities which have "Yes" under that persons name. In this example there is a maximum of 2 results but in the actual data it could be up to 10.

The name can be changed via a data validation cell - I know how to do that bit!

Thanks in advance.

 

[hagia_sofia]

Maybe this:

=FILTER($A2:$A8,B2:B8=H$2)

 

[Cubist]

Are the names always in the exact order in your example?

 

[gruntingmonkey]

The names are examples. I want the calculation (which will only be in H4 (or H5 & H6 if needed to return multiple values)) to look up the name in Row 1 and then use the relevant Column to find the "Yes" fields and then return the activity in column A that it says Yes next to.

@hagia_sofia result gets a #calc! error and will only ever look in column B. There is also no way to look at the name as it doesn't reference the names in Row 1.

 

[hagia_sofia]

Ah, my apologies, how about this then:

=FILTER($A$2:$A$8,INDEX($A$2:$E$8,,XMATCH(H$2,$A$1:$E$1))="Yes")

 

[gruntingmonkey]

If I were to add an additional column (now column A) for A new variation. would I be able to exclude anything that says "Online" in that column from the results?

=FILTER($B$2:$B$8,INDEX($B$2:$F$8,,XMATCH(I$2,$B$1:$F$1))="Yes")

I think that means having the index saying yes AND maybe a new filter into the equation?

 

[hagia_sofia]

Try:

=FILTER($B$2:$B$8,($A$2:$A$8<>"Online")*(INDEX($A$2:$F$8,,XMATCH(I$2,$A$1:$F$1))="Yes"))

 

[gruntingmonkey]

Try:

=FILTER($B$2:$B$8,($A$2:$A$8<>"Online")*(INDEX($A$2:$F$8,,XMATCH(I$2,$A$1:$F$1))="Yes"))

This work perfectly. Thank you so much for your help.