Simple formula for staff roster is baffling me...

I really need some help with a formulaic roster I can't figure out.

I have 3 staff on a Monday and I need to automate a spreadsheet to state who will work that day. The attachment contains the below:

Monday
Staff A availability = no
Staff B availability = no
Staff C availability = yes

Working on Mon = Staff C


In need a formula that states:
If staff A can work, then insert on Monday.
BUT if staff A cannot work, then check staff B availability.
If staff B can work, then insert on Monday.
BUT if staff B cannot work, then check staff C availability.
If staff C can work, then insert on Monday.
BUT if staff C cannot work, insert NEW STAFF.

I would be very appreciative if you know how the formula is written and could add it into the attached spreadsheet.

Matthew

Try this formula,

=IF(COUNTIF(C3:C5,"yes")>0,INDEX(B3:C5,MATCH("Yes",C3:C5,0),1),"New Staff")