Something like this?

=IF(AND((OFFSET('Full Survey'!D$1,(ROWS(H$3:H3)-1)*27,0)="SINGLE")),(OFFSET('Full Survey'!H$14,(ROWS(H$3:H3)-1)*27,0)="IMPROVE")),1,"")