Nested IF with AND/OR for eligibility
IntermediateTraining and HR workflows often depend on more than one rule at the same time. Excel handles this well when you combine IF with AND and OR.
In this exercise, each employee qualifies for the training program only if they have at least 2 years of tenure and either their title includes Senior or their manager recommendation is Yes.
The goal is to build one formula for the eligibility decision and a second formula that explains the result.
Your task:
- In column E, return "Eligible" or "Not eligible" for each employee based on the rule above.
- In column F, return a reason for each result:
"Meets all criteria" for eligible employees,
"Insufficient tenure" when tenure is under 2 years,
"No senior title or recommendation" when tenure is high enough but neither of the other conditions is met. - Start in row 2 and fill both formulas down through row 11.
Use the values in each row to build the logic. For the title test, decide how you want to determine whether the text contains Senior.
Need some help?
Hint 1
Build the eligibility test in two parts: one check for tenure, and a second check where either the title condition or the recommendation condition can qualify the employee.
Hint 2
SEARCH can detect whether "Senior" appears inside a longer title, and wrapping it with ISNUMBER turns that result into a TRUE/FALSE test you can use inside OR.
Nested IF with AND/OR for eligibility
IntermediateTraining and HR workflows often depend on more than one rule at the same time. Excel handles this well when you combine IF with AND and OR.
In this exercise, each employee qualifies for the training program only if they have at least 2 years of tenure and either their title includes Senior or their manager recommendation is Yes.
The goal is to build one formula for the eligibility decision and a second formula that explains the result.
Your task:
- In column E, return "Eligible" or "Not eligible" for each employee based on the rule above.
- In column F, return a reason for each result:
"Meets all criteria" for eligible employees,
"Insufficient tenure" when tenure is under 2 years,
"No senior title or recommendation" when tenure is high enough but neither of the other conditions is met. - Start in row 2 and fill both formulas down through row 11.
Use the values in each row to build the logic. For the title test, decide how you want to determine whether the text contains Senior.
Need some help?
Hint 1
Build the eligibility test in two parts: one check for tenure, and a second check where either the title condition or the recommendation condition can qualify the employee.
Hint 2
SEARCH can detect whether "Senior" appears inside a longer title, and wrapping it with ISNUMBER turns that result into a TRUE/FALSE test you can use inside OR.