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)

A B C D E F 1 staff A Mon Mon 2 staff B Mon Thurs Staff A 3 staff C Tue Thurs Staff B 4 staff D Tue Sat Staff E 5 staff E Mon Sat <colgroup><col><col span="6"></colgroup><tbody> </tbody>

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

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))),"")