Cell references adjusted per the sample workbook - it returns the correct value.
=IF(COUNTIF(INDEX($G6:$S6,MATCH(REPT("Z",255),$G6:$S6)-MIN(3,COUNTA($G6:$S6))):$S4,"*N")=4,"Yes","No")

My suggested solution is not valid given the structure of the sample workbook, but was provided before the workbook was made available.