IFERROR +1

[AjayneA]

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/C	01/01
	Mon	Tue	Wed	Thu	Fri
	01-Jan	02-Jan	03-Jan	04-Jan	05-Jan
Name	A Person	A Person 7	A Person 3	A Person 2	A Person 5
	A Person 2
	A Person 2

Sheet 2

Building ID	Name	Mon	Tue	Wed	Thu	Fri
		01-Jan	02-Jan	03-Jan	04-Jan	05-Jan
1	A Person	Duty	AM/PM			AM/PM
1	A Person 2			AM/PM	Duty
1	A Person 3			Duty
1	A Person 4	AM/PM	AM/PM
1	A Person 5	Duty				Duty
1	A Person 6			AM/PM
1	A Person 7		Duty			Duty
1	A Person 8	AM/PM
1	A Person 9	AM/PM			Duty
1	A Person 10		Duty	Duty
1	A Person 11	AM/PM
1	A Person 12			AM/PM		AM/PM
1	A Person 13			AM/PM
1	A Person 14	AM/PM				AM/PM

[#2 · Accepted 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'

[#3]

Thank you so much! This has been vexing me for over a week.

[#4]

Glad to help. Glad it works for you. and hopefully next time you won't wait a week before reaching out 🙂