I am developing a scrub field that needs to compare cell A value to 1 of 3 sets of lists and cell B value to a different 1 of 3 sets of lists. THEN the list answer to comparison 1 needs to equal the answer to comparison 2. The final value should equal either “GOOD” or “CHECK”. See attached spreadsheet.

Scrub Sample.xls

If a value is chosen from the list in cell Request!A2 and a value is chosen from the list in Request!B2, the scrub field in Request!C2 needs to compare if the “Leave Reason” matches an acceptable “Leave Relationship”.

All lists are represented on the ‘Reference’ worksheet:
Reason1 List = Relation1 List
Reason2 List = Relation2 List
Reason3 List = Relation3 List

Examples:
IF Request!A2 = Employee’s Serious Health Condition, THEN = Reason1 list. And if Request!B2 = Self / Employee, THEN Relation1 list. Which equals a final result of “GOOD”.

IF Request!A2 = Maternity, THEN = Reason1 list. And if Request!B2 = Child Under 18 Years of Age, THEN Relation2 list. Which equals a final result of “CHECK”.

IF Request!A2 = Family Member’s Serious Health Condition, THEN = Reason2 list. And if Request!B2 = Child Under 18 Years of Age, THEN Relation2 list and Relation3 list (if two different results is not possible for the formula to work, I can eliminate Reason3 and Relation3 List from the formula). Since, ‘Child Under 18 Years of Age’ is a value on two different list but Reason2 list = Relation2 list, which equals a final result of “GOOD”.