A lot of Conditions

[anneb_87]

Hi Experts!

I have a lot of rows to determine the Plan Name and Tier. What excel formula should I use?

Here is the sample output see column FINAL PLAN NAME and Tier

Book1.xlsx
	E	F	G	H	I
2	Member ID	Dependent Type	Plan Name	FINAL PLAN NAME	Tier
3	A	2. Female Subscriber	Waived	Waived	EE + Fam
4	A	6. Daughter	Waived	Waived	EE + Fam
5	A	3. Husband	Waived	Waived	EE + Fam
6	A	5. Son	Waived	Waived	EE + Fam
7	B	6. Daughter	Plan 4 - CA	PPO Plan 4 - CA	EE + Fam
8	B	6. Daughter	Plan 4 - O/A	PPO Plan 4 - CA	EE + Fam
9	B	1. Male Subscriber	Plan 4 - CA	PPO Plan 4 - CA	EE + Fam
10	B	4. Wife	Plan 4 - CA	PPO Plan 4 - CA	EE + Fam
11	C	4. Wife	Waived	PPO Plan 4 - CA	Employee
12	C	1. Male Subscriber	Plan 4 - CA	PPO Plan 4 - CA	Employee
13	D	5. Son	Waived	Kaiser	Employee
14	D	2. Female Subscriber	Kaiser	Kaiser	Employee
15	D	2. Female Subscriber	Plan 4 - CA	PPO Plan 4 - CA	EE + SP
16	D	3. Husband	Plan 4 - O/A	PPO Plan 4 - CA	EE + SP
17	F	3. Husband	Waived	Kaiser	Employee
18	F	2. Female Subscriber	Kaiser	Kaiser	Employee
19	G	3. Husband	Waived	Kaiser	Employee
20	G	2. Female Subscriber	Kaiser	Kaiser	Employee
21	H	6. Daughter	Plan 4 - O/A	PPO Plan 4 - CA	EE + CH
22	H	2. Female Subscriber	Plan 4 - CA	PPO Plan 4 - CA	EE + CH
23	H	5. Son	Plan 4 - CA	PPO Plan 4 - CA	EE + CH
24	I	2. Female Subscriber	Waived	waived	waived
25	I	3. Husband	Kaiser	waived	waived
26	J	6. Daughter	Waived	Kaiser	EE + SP
27	J	3. Husband	Kaiser	Kaiser	EE + SP
28	J	2. Female Subscriber	Kaiser	Kaiser	EE + SP
29	K	4. Wife	Waived	Kaiser	Employee
30	K	1. Male Subscriber	Kaiser	Kaiser	Employee
31	L	4. Wife	Waived	PPO Plan 4 - CA	Employee
32	L	1. Male Subscriber	Plan 4 - CA	PPO Plan 4 - CA	Employee
33	M	2. Female Subscriber	Plan 4 - CA	PPO Plan 4 - CA	Employee
34	M	3. Husband	Waived	PPO Plan 4 - CA	Employee
35	N	3. Husband	Waived	Kaiser	Employee
36	N	6. Daughter	Waived	Kaiser	Employee
37	N	2. Female Subscriber	Kaiser	Kaiser	Employee
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:E37	Cell Value	contains "false"	text	NO

Here are the conditions

6. Census - GCJBT 2021 - PvG BONCH.xlsx
	E	F	G
2	Tier Conditions
3	Tier	Dependent Type	Plan Name
4	Employee	1. Male Subscriber OR 2. Female Subscriber	Not waived
5	ESP	(1. Male Subscriber + 4. Wife) OR (2. Female Subscriber + 3. Husband) OR (1. Male Subscriber + 8. Female Partner) OR (2. Female Subscriber + 7. Male Partner)	Not waived
6	Employee	(1. Male Subscriber + 4. Wife) OR (2. Female Subscriber + 3. Husband) OR (1. Male Subscriber + 8. Female Partner) OR (2. Female Subscriber + 7. Male Partner)	Dependents Plan Name is waived
7	ECH	(1. Male Subscriber + 5.Son\6.Daugther) OR (2. Female Subscriber + 5.Son\6.Daugther)	Not waived
8	Employee	(1. Male Subscriber + 5.Son\6.Daugther) OR (2. Female Subscriber + 5.Son\6.Daugther)	EE is not waived then all dependent is Waived
9	ECH	(1. Male Subscriber + 5.Son\6.Daugther) OR (2. Female Subscriber + 5.Son\6.Daugther)	EE is not waived and one of the dependent is not waived
10	FAM	(1. Male Subscriber + 4. Wife + 5.Son\6.Daugther) OR (2. Female Subscriber + 3. Husband + 5.Son\6.Daugther) OR (1. Male Subscriber + 8. Female Partner + 5.Son\6.Daugther) OR (2. Female Subscriber + 7. Male Partner + 5.Son\6.Daugther)	Not waived
11	Employee	(1. Male Subscriber + 4. Wife + 5.Son\6.Daugther) OR (2. Female Subscriber + 3. Husband + 5.Son\6.Daugther) OR (1. Male Subscriber + 8. Female Partner + 5.Son\6.Daugther) OR (2. Female Subscriber + 7. Male Partner + 5.Son\6.Daugther)	Ony the subscriber is not waived. all dependents are waived
12	ESP	(1. Male Subscriber + 4. Wife + 5.Son\6.Daugther) OR (2. Female Subscriber + 3. Husband + 5.Son\6.Daugther) OR (1. Male Subscriber + 8. Female Partner + 5.Son\6.Daugther) OR (2. Female Subscriber + 7. Male Partner + 5.Son\6.Daugther)	Subscriber and Spouse\Partner are not waived. All children are waived
13	ECH	(1. Male Subscriber + 4. Wife + 5.Son\6.Daugther) OR (2. Female Subscriber + 3. Husband + 5.Son\6.Daugther) OR (1. Male Subscriber + 8. Female Partner + 5.Son\6.Daugther) OR (2. Female Subscriber + 7. Male Partner + 5.Son\6.Daugther)	Subscriber and one of the children are not waived. All Spouse\Partner are waived
14	waived	any combination	If Subscriber is waived everyone will be waived
15
16	Plan Conditions
17	FINAL PLAN NAME	Plan Conditions
18	Waived	If Subscriber is waived everyone will be waived
19	Specific Plan Name	Will follow the Subscriber Plan Name
Sheet2

 

[anneb_87]

Anyone? Please huhuhuhuhu