Copper Contributor

I am trying to create a spreadsheet to show me who the duty person is on a particular day. I have managed to get my formula to show me the name of the days' duty person however some days there are two people on duty and I have tried to add +1 into my formula however this is now just showing me the next name in the list, not the next duty person.


Sheet 1 =IFERROR(INDEX(Sheet2!$C$5:$C$18,MATCH("Duty",Sheet2!D5:D18,0)),"")

NameA PersonA Person 7A Person 3A Person 2A Person 5
 A Person 2    
 A Person 2    


Sheet 2

Building IDNameMonTueWedThuFri
1A PersonDutyAM/PM  AM/PM
1A Person 2  AM/PMDuty 
1A Person 3  Duty  
1A Person 4AM/PMAM/PM   
1A Person 5Duty   Duty
1A Person 6  AM/PM  
1A Person 7 Duty  Duty
1A Person 8AM/PM    
1A Person 9AM/PM  Duty 
1A Person 10 DutyDuty  
1A Person 11AM/PM    
1A Person 12  AM/PM AM/PM
1A Person 13  AM/PM  
1A Person 14AM/PM   AM/PM
3 Replies
best response confirmed by AjayneA (Copper Contributor)

@AjayneA if you have 365 then you can use:


and it will 'spill' the list of all people listed on 'duty'

Thank you so much! This has been vexing me for over a week.
Glad to help. Glad it works for you. and hopefully next time you won't wait a week before reaching out :)