Extracting data from a list with multiple criteria

[kr0nic]

Hello all,

this is for Excel 2010.

Column A - list of staff
Column B, C, D, E - day of the week they can work (Mon, Tue, Thurs, Sat only)

[TABLE="width: 407"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD] [TABLE="class: grid, width: 432"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]staff A[/TD]
[TD]Mon[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Mon[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]staff B[/TD]
[TD]Mon[/TD]
[TD] [/TD]
[TD]Thurs[/TD]
[TD] [/TD]
[TD]Staff A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]staff C[/TD]
[TD] [/TD]
[TD]Tue[/TD]
[TD]Thurs[/TD]
[TD] [/TD]
[TD]Staff B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]staff D[/TD]
[TD] [/TD]
[TD]Tue[/TD]
[TD] [/TD]
[TD]Sat[/TD]
[TD]Staff E[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]staff E[/TD]
[TD]Mon[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Sat[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want column F to display all the staff for a particular day when I input the day into F1.

Please help! many thanks

 

[Aladin Akyurek]

F2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$1:$A$5,SMALL(IF(1-(INDEX($B$1:$E$5,0,
   MATCH(F$1,{"Mon","Tue","Thurs","Sat"},0))=""),
   ROW($A$1:$A$5)-ROW($A$1)+1),ROWS($F$2:F2))),"")