I have a workbook with a UM_B sheet for data entry or pasting from another
excel book.
It has a column E4:E2300 (Carriers) that has data validation set for
criteria which is another sheet List!E2:E3470

If doing manual data entry it gives you error for invalid Carriers however
when pasting data in Carriers that are not on list are getting in with no
errors thus I made another column A with Vlookup
For valid carrier
blanks
and invalid carriers
Formulas work but not for 3 options

=IF(E433=VLOOKUP(E433,List!$E$2:$F$3475,1,FALSE)," ", "Remove")
=IF(AND(E7<VLOOKUP(E7,List!$E$2:$F$3475,1,FALSE), E7=""),"REMOVE","")
=IF(E437="","Remove",VLOOKUP(E437,List!$E$2:$F$347 5,1,FALSE))

=(IF(OR(E422=VLOOKUP(E422,List!$E$2:$F$3475,1,FALS E),E422),IF(E422<VLOOKUP(E422,List!$E$2:$F$3475,1 ,FALSE),"
"),IF(E424="","",VLOOKUP(E424,List!$E$2:$F$3475,1, FALSE))))

Also need to look at second column in um_b as part of lookup
if data in column d and no data in column e

"JC" wrote:

I have a workbook with a UM_B sheet for data entry or pasting from another
excel book.
It has a column E4:E2300 (Carriers) that has data validation set for
criteria which is another sheet List!E2:E3470

If doing manual data entry it gives you error for invalid Carriers however
when pasting data in Carriers that are not on list are getting in with no
errors thus I made another column A with Vlookup
For valid carrier
blanks
and invalid carriers
Formulas work but not for 3 options

=IF(E433=VLOOKUP(E433,List!$E$2:$F$3475,1,FALSE)," ", "Remove")
=IF(AND(E7<VLOOKUP(E7,List!$E$2:$F$3475,1,FALSE), E7=""),"REMOVE","")
=IF(E437="","Remove",VLOOKUP(E437,List!$E$2:$F$347 5,1,FALSE))

=(IF(OR(E422=VLOOKUP(E422,List!$E$2:$F$3475,1,FALS E),E422),IF(E422<VLOOKUP(E422,List!$E$2:$F$3475,1 ,FALSE),"
"),IF(E424="","",VLOOKUP(E424,List!$E$2:$F$3475,1, FALSE))))