Simplifying AND/OR formula

[Tigerexcel]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1		1/01/2021	2/01/2021	3/01/2021	4/01/2021	5/01/2021	6/01/2021	7/01/2021	8/01/2021	9/01/2021	10/01/2021	11/01/2021	12/01/2021	13/01/2021	14/01/2021	15/01/2021	16/01/2021	17/01/2021	18/01/2021	19/01/2021	20/01/2021	21/01/2021	22/01/2021	23/01/2021	24/01/2021	25/01/2021	26/01/2021	27/01/2021	28/01/2021
2	Jane	Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
3	Bob	Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
4	Pete	Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
5	Harry	Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
6
7
8
9		TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE
10		FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE
11		FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
12		FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE
13
14
15													Required
16													Jane	Mon	Tue
17													Bob	Wed
18													Pete
19													Harry			Fri
Sheet1

Cell Formulas
Range	Formula
B9:AC12	B9	=AND($A2=$M16,OR(B2=$N16,B2=$O16,B2=$P16,B2=$Q16))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9:AC12	Expression	=AND($A2=$M16,OR(B2=$N16,B2=$O16,B2=$P16))	text	NO
B2:AC5	Expression	=AND($A2=$M16,OR(B2=$N16,B2=$O16,B2=$P16,B2=$Q16))	text	NO

I need to modify the formula so that it ignores any blanks in the Required for work table starting in cell M15. Also, I have quite a number of ORs in my formula, is there a way to shorten this formula as this could get quite long in practice (for the sake of the example it only has Mon to Sun). Please ignore the TRUE/FALSE rows, that was there to help guide me with the CF.
Just to be clear, Columns G,H, N,O etc should not return true, I've tried NOT(ISBLANK) but couldn't get the desired result.

 

[Bo_Ry]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1		Fri 01-01-21	02-01-21	03-01-21	04-01-21	05-01-21	06-01-21	07-01-21	08-01-21	09-01-21	10-01-21	11-01-21	12-01-21	13-01-21	14-01-21	15-01-21	16-01-21	17-01-21	18-01-21	19-01-21	20-01-21	21-01-21	22-01-21	23-01-21	24-01-21	25-01-21	26-01-21	27-01-21	28-01-21
2	Jane	Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
3	Bob	Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
4	Pete	Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
5	Harry	Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri			Mon	Tue	Wed	Thu	Fri
6
7
8
9		1	1	0	0	0	0	0	1	1	0	0	0	0	0	1	1	0	0	0	0	0	1	1	0	0	0	0	0
10		0	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0
11		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12		0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	1	0	0
13
14
15													Required
16		0	0	0	1	1	0						Jane	Mon	Tue
17		0	0	0	0	0	1						Bob	Wed
18		0	0	0	0	0	0						Pete
19		TEXT(B$1,"ddd")[/COLOR])]1	0	0	0	0	0						Harry			Fri
Sheet1

Cell Formulas
Range	Formula
B9:AC12	B9	=COUNTIF(INDEX($N$16:$Q$19,MATCH($A2,$M$16:$M$19,),),B2)
B16:G19	B16	=COUNTIF(INDEX($N$16:$Q$19,MATCH($A2,$M$16:$M$19,),),TEXT(B$1,"ddd"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AC5	Expression	=COUNTIF(INDEX($N$16:$Q$19,MATCH($A2,$M$16:$M$19,),),B2)	text	NO

1 Jan 2021is Friday , You may use TEXT(B$1,"ddd") instead of B2

 

[Tigerexcel]

Thanks Bo_Ry.