This should sort the Look up error. My apologies. I was using a named range and forgot to convert it back after testing

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_C ontract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))