extract all name if certain cell has certain Text

[hajiali]

[TABLE="width: 500"]
<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]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]NAME[/TD]
[TD]SUNTIME[/TD]
[TD]SUNP0S[/TD]
[TD]MONTIME[/TD]
[TD]MONP0S[/TD]
[TD]TUETIME[/TD]
[TD]TUEPOS[/TD]
[TD]WEDTIME[/TD]
[TD]WEDPOS[/TD]
[TD]THUTIME[/TD]
[TD]THUPOS[/TD]
[TD]FRITME[/TD]
[TD]FRIPOS[/TD]
[TD]SATTIME[/TD]
[TD]SATPOS[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JOHN
[/TD]
[TD]4-12[/TD]
[TD]POS1
[/TD]
[TD]4-12[/TD]
[TD]POS4[/TD]
[TD]4-12[/TD]
[TD]POS4[/TD]
[TD]4-12[/TD]
[TD]POS4[/TD]
[TD]4-12[/TD]
[TD]POS4[/TD]
[TD]4-12[/TD]
[TD]POS4[/TD]
[TD]4-12[/TD]
[TD]POS1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BILL[/TD]
[TD]6-15[/TD]
[TD]POS2
[/TD]
[TD]6-15[/TD]
[TD]POS1[/TD]
[TD]6-15[/TD]
[TD]EXTRA[/TD]
[TD]6-15[/TD]
[TD]EXTRA[/TD]
[TD]6-15[/TD]
[TD]EXTRA[/TD]
[TD]6-15[/TD]
[TD]EXTRA[/TD]
[TD]6-15[/TD]
[TD]POS2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TOM[/TD]
[TD]14-23
[/TD]
[TD]POS3[/TD]
[TD]14-23[/TD]
[TD]POS2[/TD]
[TD]14-23[/TD]
[TD]POS1[/TD]
[TD]14-23[/TD]
[TD]EXTRA[/TD]
[TD]14-23[/TD]
[TD]EXTRA[/TD]
[TD]14-23[/TD]
[TD]EXTRA[/TD]
[TD]14-23[/TD]
[TD]POS3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PETER[/TD]
[TD]15-00[/TD]
[TD]EXTRA[/TD]
[TD]15-00[/TD]
[TD]EXTRA[/TD]
[TD]15-00[/TD]
[TD]EXTRA[/TD]
[TD]15-00[/TD]
[TD]POS1[/TD]
[TD]15-00[/TD]
[TD]POS3[/TD]
[TD]15-00[/TD]
[TD]POS3[/TD]
[TD]15-00[/TD]
[TD]EXTRA[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]MARK[/TD]
[TD]7-17[/TD]
[TD]EXTRA[/TD]
[TD]7-17[/TD]
[TD]EXTRA[/TD]
[TD]7-17[/TD]
[TD]POS3[/TD]
[TD]7-17[/TD]
[TD]POS2[/TD]
[TD]7-17[/TD]
[TD]POS1[/TD]
[TD]7-17[/TD]
[TD]POS2[/TD]
[TD]7-17[/TD]
[TD]EXTRA[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]MATT[/TD]
[TD]4-12[/TD]
[TD]POS4[/TD]
[TD]4-12[/TD]
[TD]EXTRA[/TD]
[TD]4-12[/TD]
[TD]EXTRA[/TD]
[TD]4-12[/TD]
[TD]POS3[/TD]
[TD]4-12[/TD]
[TD]POS2[/TD]
[TD]4-12[/TD]
[TD]POS1[/TD]
[TD]4-12[/TD]
[TD]POS4[/TD]
[/TR]
</tbody>[/TABLE]

above table I have names and shift (in Red) next to each shift is a position that each employee will work. to the Right of the table COLUMN P1 I will put days of week (ex: "SUNDAY", "MONDAY", ETC.) below that I want it to pull all the names of employees that are "EXTRA"


TABLE SHOULD READ FOR MONDAY
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MONDAY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PETER
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MARK[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]MATT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If i typed TUESDAY than list should be: BILL, PETER, MATT

any suggestions kindly appreciated it.

 

[Fluff]

In that case put
=MATCH(TEXT($C$1,"ddd")&"POS",Bid!$A$4:$P$4,0)
in a spare cell & use this
=IFERROR(INDEX(Bid!$A$5:$P$10,SMALL(IF(INDEX(Bid!$A$5:$P$10,,$A$12)="EXTRA",ROW(Bid!$A$5:$A$10)-ROW(Bid!$A$5)+1),ROWS(Bid!$A$4:B4)),$A$12-1),"")
change the $A$12 to wherever you put the Match formula

 

[hajiali]

cell S5 = MATCH(TEXT($C$1,"ddd")&"POS",Bid!$A$4:$P$4,0)

as instructed

I got

=IFERROR(INDEX(BID!$A$5:$P$10,SMALL(IF(INDEX(BID!$A$5:$P$10,,$S$5)="EXTRA",ROW(BID!$A$5:$A$10)-ROW(BID!$A$5)+1),ROWS(BID!$A$4:B5)),$S$5-1),"")

after pressing Ctrl + Shift + Enter getting Blank

 

[hajiali]

GOT IT!!! had to change the range:

TO

=IFERROR(INDEX(BID!$A$5:$P$434,SMALL(IF(INDEX(BID!$A$5:$P$434,,$S$5)="EXTRA",ROW(BID!$A$5:$A$434)-ROW(BID!$A$5)+1),ROWS(BID!$A$4:B4)),$S$5-1),"")

 

[Fluff]

As you have the helper cell, you could change the original formula to
=IFERROR(INDEX(BID!$A$5:$A$434,SMALL(IF(INDEX(BID!$A$5:$P$434,,$S$5)="EXTRA",ROW(BID!$A$5:$A$434)-ROW(BID!$A$5)+1),ROWS(BID!$A$4:A4))),"")
to save calculating it again.