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
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]11[/TD]
[TD]boy[/TD]
[/TR]
[TR]
[TD]sean[/TD]
[TD]19[/TD]
[TD]adult male[/TD]
[/TR]
[TR]
[TD]susan[/TD]
[TD]14[/TD]
[TD]girl[/TD]
[/TR]
[TR]
[TD]becky[/TD]
[TD]18[/TD]
[TD]adult female[/TD]
[/TR]
[TR]
[TD]steve[/TD]
[TD]11[/TD]
[TD]adult male[/TD]
[/TR]
[TR]
[TD]sherry[/TD]
[TD]14[/TD]
[TD]adult female[/TD]
[/TR]
[TR]
[TD]ann[/TD]
[TD]14[/TD]
[TD]girl[/TD]
[/TR]
[TR]
[TD]mark[/TD]
[TD]11[/TD]
[TD]adult male[/TD]
[/TR]
[TR]
[TD]blake[/TD]
[TD]11[/TD]
[TD]adult male[/TD]
[/TR]
[TR]
[TD]shannon[/TD]
[TD]14[/TD]
[TD]girl[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Boy[/TD]
[TD]Girl[/TD]
[TD]Adult Male[/TD]
[TD]Adult Female[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So end result should look like this

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Boy[/TD]
[TD]Girl[/TD]
[TD]Adult Male[/TD]
[TD]Adult Female[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD]steve
mark
blake[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD]sean[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]susan
ann
shannon[/TD]
[TD][/TD]
[TD]Sherry[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]becky[/TD]
[/TR]
</tbody>[/TABLE]
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.