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.

 

[steve the fish]

Enter this CTRL-SHIFT-ENTER

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(INDEX($A$2:$O$7,,MATCH(LEFT(P$1,3)&"POS",$A$1:$O$1,0))="EXTRA",ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($A$1:A1))),"")

Drag down until no more entries. Make sure MONPOS says MONPOS not MONP0S

 

[kweaver]

I tried another approach filled down from P2:

=IF("EXTRA"=INDEX($A$1:$O$7,ROW(),MATCH(LEFT($P$1,3)&"POS",$A$1:$O$1,0)),A2,"")

 

[hajiali]

Enter this CTRL-SHIFT-ENTER

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(INDEX($A$2:$O$7,,MATCH(LEFT(P$1,3)&"POS",$A$1:$O$1,0))="EXTRA",ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($A$1:A1))),"")

Drag down until no more entries. Make sure MONPOS says MONPOS not MONP0S

using what you gave me in putting it in my actual situation i came up with

=IFERROR(INDEX(BID!$A$5:$A$434,SMALL(IF(INDEX(BID!$A$5:$P$434,,MATCH(TEXT(C1,"ddd")&"POS",BID!$A$4:$P$4,0))="EXTRA",ROW(BID!$A$5:$A$434)-BID!$A$5+1),ROWS(BID!$A$4:A4))),"")

So Bid Sheet would have the above Table starting in A4 Cell and adding a column to the right Of Name column. C1 = (DATE(J1,G1,H1),"") which puts the day of week based so j1,G1,H1 Value. I had to change Match to Match(Text(C1,"DDD")&"POS"), I check to make sure

Text(C1,"DDD")&"POS"=BID!$A$4:$P$4

it shows invalid. but

with C1 reading "SUNDAY"

Text(C1,"DDD")&"POS"= BID!D4

results shows "True"

 

[Fluff]

If you just put
=MATCH(TEXT(C1,"ddd")&"POS",BID!$A$4:$P$4,0)
into a cell what does it return?

 

[hajiali]

it returns number 4

 

[Fluff]

Are you columns laid out as shown in your op?
Because if it returns 4 thats the column labelled MONTIME not MONPOS

 

[hajiali]

so when c1 = Sunday = 4
Monday = 6
Tuesday = 8
Wednesday = 10
Thursday = 12
Friday = 14
Saturday = 16

those number corresponds with the column the SUNPOS, MONPOS, TUEPOS, WEDPOS form my Bid sheets starting with Column A being the 1st column the SUNPOS, MONPOS, TUEPOS, WEDPOS form my Bid sheets

 

[Fluff]

In that case that's ok. In way doesn't the formula work for you?

 

[hajiali]

after pressing ctrl+Shift+enter I get Blank in the cells.