Lookup/Filter?

[russelljones]

Morning all!!

I have searched and tried and error'd on the below- any help you can provide would be greatly appreciated!!
I have a table containing a list of employees and dates training they have completed a follows:

[TABLE="width: 690"]
<colgroup><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD][/TD]
[TD]H&S[/TD]
[TD]ICT[/TD]
[TD]Security[/TD]
[TD]Data Compliance[/TD]
[TD]Other training[/TD]
[TD]More Training[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]John Smith[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD][/TD]
[TD="align: right"]10/09/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD]Joe Bloggs[/TD]
[TD="align: right"]05/05/2018[/TD]
[TD][/TD]
[TD="align: right"]10/09/2018[/TD]
[TD="align: right"]25/01/2017[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4 [/TD]
[TD]Richard Smith[/TD]
[TD="align: right"]02/12/2017[/TD]
[TD="align: right"]05/08/2017[/TD]
[TD][/TD]
[TD="align: right"]25/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5 [/TD]
[TD]Amanda Jones[/TD]
[TD][/TD]
[TD="align: right"]04/08/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Jan-19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6 [/TD]
[TD]Racheal Smith[/TD]
[TD][/TD]
[TD="align: right"]04/08/2019[/TD]
[TD][/TD]
[TD="align: right"]09/03/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





From this, I have created a new sheet, and want to run some kind of filter/lookup whereby I can select the relevant training from a drop down list, and it return a list of employees who have that training and the date they completed it, as follows:


[TABLE="width: 435"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Select Training Type:[/TD]
[TD]H&S[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]John Smith[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]Joe Bloggs[/TD]
[TD="align: right"]05/05/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]Richard Smith[/TD]
[TD="align: right"]02/12/2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thank you all in advance!
Russ

 

[Fluff]

How about
In B3 copied down
=IFERROR(INDEX(Sheet1!$A$2:$A$6,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1)/((Sheet1!$B$1:$F$1=$C$1)*(Sheet1!$B$2:$F$6<>"")),ROWS($A$1:$A1))),"")
In C3 copied down
=IFERROR(INDEX(Sheet1!$B$2:$G$6,MATCH(B3,Sheet1!$A$2:$A$6,0),MATCH($C$1,Sheet1!$B$1:$G$1,0)),"")

 

[russelljones]

Fluff,
Thank you, fantastic, much appreciated!

 

[Fluff]

Glad to help & thanks for the feedback