multi column/row match from one sheet and populate data on another

[ShadowHunter]

I want to fill in Sheet2 with Sheet1 Column A that match Sheet1 Column B to Sheet2 Column A and Sheet1 Column C matched to Sheet2 Row 1.

Data on Sheet1

column A	Column B	Column C
Mike	11	boy
sean	19	adult male
susan	14	girl
becky	18	adult female
steve	11	adult male
sherry	14	adult female
ann	14	girl
mark	11	adult male
blake	11	adult male
shannon	14	girl

<tbody>
</tbody>

Data on Sheet 2 has criteria to match on Row 1 and Column A

	Boy	Girl	Adult Male	Adult Female
11
19
14
18

<tbody>
</tbody>

So end result should look like this

Name	Boy	Girl	Adult Male	Adult Female
11	Mike		steve mark blake
19			sean
14		susan ann shannon		Sherry
18				becky

<tbody>
</tbody>

My feable attempt on the first cell match up... not going good. Obviously I need lots of help

My try consisted of Index(Sheet1!A:A,MATCH(Sheet2!A2,Sheet1!C)*(Sheet2!B1,Sheet!B),0)
my try doesn't even touch concatenating multiple matches into a single cell...

 

[svendiamond]

I can get it to work for one match but multiple involves a little more work. First I entered this into Sheet2 cell B2, and filled down and across the table.

=IFERROR(INDEX(Sheet1!$A$1:$A$10,MATCH($A2,IF(Sheet1!$C$1:$C$10=B$1,Sheet1!$B$1:$B$10),0)),"")

If you want to return multiple matches you could use this link: Use INDEX to lookup multiple values in a list. Learn | Five Minute Lessons to help you come up with a formula (but this would involve having a list of all the ones that match "11" and "boy" in one column, "19" and "boy" in another column, etc. etc.)... if you want to then concatenate them all into one cell it would get even crazier. I would love to help but I am actually supposed to be doing homework right now. Maybe someone else can come up with a better idea. Good luck.