List of unused staff

[tigerzen]

Book2
	B	C	D	E	F	G	H	I	J	K	L
2		Mon	Tues	Wed	Thu	Fri	Sat	Sun		List
3	AM		Ed LC				LC	Ed		Ed	5
4	Lunch	LC	LC	LC	Ed	LC	DC	Tim		LC	7
5	Dinner	Tim	Tim Ed	Tim	Tim	Tim	Ed	LC		Tim	6
6										DC	1
7
8		Not listed	Not listed	Not listed	Not listed	Not listed	Not listed	Not listed
9		Ed	DC
10		DC
Sheet1

Cell Formulas
Range	Formula
L3:L6	L3	=COUNTIF($C$3:$I$5,"*"&K3&"*")

I'm after a formula/function on Excel 2019 that returns the staff in column K who are not scheduled as per Row 9 & 10 for each day. I've put the desired output in for Monday and Tuesday.

 

[FormR]

Hi, here's one option you can try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1
2			Mon	Tues	Wed	Thu	Fri	Sat	Sun		List
3		AM		Ed LC				LC	Ed		Ed	5
4		Lunch	LC	LC	LC	Ed	LC	DC	Tim		LC	7
5		Dinner	Tim	Tim Ed	Tim	Tim	Tim	Ed	LC		Tim	6
6											DC	1
7
8			Not listed	Not listed	Not listed	Not listed	Not listed	Not listed	Not listed
9			Ed	DC	Ed	LC	Ed	Tim	DC
10			DC		DC	DC	DC
11
12
13
Sheet1

Cell Formulas
Range	Formula
L3:L6	L3	=COUNTIF($C$3:$I$5,"*"&K3&"*")
C9:I13	C9	=IFERROR(INDEX($K$3:$K$6,AGGREGATE(15,6,(ROW($K$3:$K$6)-MIN(ROW($K$3:$K$6))+1)/(1-ISNUMBER(MATCH("* "&$K$3:$K$6&" *"," "&C$3:C$5&" ",0))),ROWS(C$9:C9))),"")

 

[tigerzen]

Thanks FormR, that does the trick. Your formula is fairly advanced and will take me a little to digest, it does highlight the advantage of using Office 365 where a simple Filter and Match will achieve the same thing. Much appreciated FormR.

 

[FormR]

it does highlight the advantage of using Office 365

Yes, the new functions have made this type of thing much easier