Extract data from a Table

Guys I need help in extracting data from a table.


Excel pic.jpg
eg.

A-team B-team D-team C-team
sam X X
pat X X
john X X
peter X

I want to know what are the teams in which sam a member. Peoples' names should be selected from a dropdown menu and names of the teams should be displayed below the menu in alphabatical order.

Can u help me?

Here's a formulas play to deliver your intents ...

Assume your source table as pictured is in A1:E5
names in A2:A5, Team names in B1:E1, with "x" inputs within B2:E2

In H1: =INDEX($A$2:$A$5,COLUMNS($A:A))
Copy across to K1
In H2: =IF(OFFSET($B$2,COLUMNS($A:A)-1,ROWS($1:1)-1)="x",ROWS($1:1),"")
Copy across to K2, fill down to K5

Now, assume you have a DV list in A8 to select the name, eg: sam, pat, etc
Put in A9:
=IF(ROWS($1:1)>COUNT(OFFSET($G$2:$G$5,,MATCH($A$8,$H$1:$K$1,0))),"",INDEX($B$1:$E$1,SMALL(OFFSET($G$2:$G$5,,MATCH($A$8,$H$1:$K$1,0)),ROWS($1:1))))
Copy down to A12. This returns the results (ie the Team names) for the name selected in A8, all neatly packed at the top. Select another name from the DV to test it out, also test by re-doing the "x" inputs within B2:E5, it should work fine
------------------------------------------------
Success? Wave it, click on the little star at the bottom left of my responses

Pl see attached file.

Reply to post #3
... Believe your startpoint as to the source data layout differs considerably from what the OP presented in the jpg (Excel pic.jpg) ?

Pl see attached file.

Guys th ank you very much for help.
further, can we get the results other way around also? I mean if i give the team can i get the members by changing the formula?

Pl see attached file.

Thanks friend