I need help on which function to use please !!

[Zee]

Hi to all the much-smarter-people than me out there!!

I've got a spreadsheet that I want to automatically insert names into a
sheet based on different shifts they work. It looks like this

# Name Surname Monday Tuesday

3 Mary Zwane DAY
4 Dean Daffue NIGHT
5 Given Mkhize DOUBLE

etc

I need a formula that will autmatically put their names into another
worksheet based on the words DAY, NIGHT or DOUBLE.

If they put in the word DOUBLE, their name needs to appear in DAY & NIGHT


e.g.

Monday Tuesday
(DAY)
Mary
Given

(NIGHT)
Dean
Given

I've tried different formulas, but because there are more than one result,
all the funtions return either FALSE or VALUE#.

Can someone please help ???

 

[Teethless mama]

Day and Double
A2:
=IF(ISERR(SMALL(IF((Monday="DAY")+(Monday="DOUBLE"),ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF((Monday="DAY")+(Monday="DOUBLE"),ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))))

Night and Double
A20:
=IF(ISERR(SMALL(IF((Monday="NIGHT")+(Monday="DOUBLE"),ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF((Monday="NIGHT")+(Monday="DOUBLE"),ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))))

Both formulae are required ctrl+shift+enter, not just enter
copy down

 

[Zee]

Hi Teethless Mama

Thanks for the formula, but I'm having trouble getting it to work.

I typed it into my worksheet, and referenced all the necessary cells to the
right places, but it keeps on giving me a blank answer and if you analyze it,
it says Formula result = Volatile.

I've gone and looked at the stuff you inserted into the formula, but I'm
battling! Can you help or am I useless??

 

[Zee]

Can anybody help - please??