SOLVED

IFERROR +1

New 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)),"")

W/C01/01    
      
 MonTueWedThuFri
 01-Jan02-Jan03-Jan04-Jan05-Jan
NameA PersonA Person 7A Person 3A Person 2A Person 5
 A Person 2    
 A Person 2    
      

 

Sheet 2

Building IDNameMonTueWedThuFri
  01-Jan02-Jan03-Jan04-Jan05-Jan
       
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 (New Contributor)
Solution

@AjayneA if you have 365 then you can use:

=FILTER(Sheet2!$C$5:$C$18,"Duty"=Sheet2!D5:D18,"")

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