if vlookup for multiple criteria for vdata validation
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)))) |
if vlookup for multiple criteria for vdata validation
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)))) |
All times are GMT +1. The time now is 05:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com