4 data sets, if equal a certain criteria then this

[Rich1362]

Hi everyone,

I don't think I can explain what I'm looking for, but I will do my best.

CRITERIA:
If Cell A1 equals 1 of 50 states
AND
Cell A2 equals 1 of 3 Worker Categories
AND
Cell A3 equals a 1 of 2 FLSA types
AND
Cell A4 equals a 1 of 200 jobs
THEN
A5 equals 1 of 7 Pay Classes

Example: (Using the data below)
#1
If you're in Iowa(non-ca), you're Part Time, Non Exempt and your job title is not Data Associate, then your Pay Class is Other States HRLY
#2
If you're in any state, full time, exempt, regardless of job, then your pay class is SALARY1
#3
If you're in California, Part Time, non exempt and your job title is Data Associate, then you're pay Class is CALIFORNIA HOURLY MOBILE

Help
I know there has to be a way to do this formula But I just can't figure it out. I need you're help. I will send you a High 5 from my thoughts and a huge smile of appreciation. Thank you so much everyone.

Worked-In State	Worker Category Description	FLSA	Job Title	Pay Class
California	Part Time	RFTN-non exempt	Data Associate	CALIFORNIA HOURLY MOBILE
California	Part Time	RFTN-non exempt	Any Job	CALIFORNIA HOURLY
California	Full Time	RFTN-non exempt	Any Job	CA HOURLY BENEFITS
California	Temp	RFTN-non exempt	Any Job	CA HOURLY BENEFITS
Non-CA	Part Time	RFTN-non exempt	Data Associate	OTHER STATES HRLY MOBILE
Non-CA	Part Time	RFTN-non exempt	Any Job	OTHER STATES HOURLY
Non-CA	Full Time	RFTN-non exempt	Any Job	OTHER HOURLY BENEFITS
Non-CA	Temp	RFTN-non exempt	Any Job	OTHER HOURLY BENEFITS
Any State	Full Time	RFTE-exempt	Any Job	SALARY1

 

[jasonb75]

This looks like it works

Book1
	A	B	C	D	E
1	Worked-In State	Worker Category Description	FLSA	Job Title	Pay class
2	California	Part Time	RFTN-non exempt	Data Associate	CALIFORNIA HOURLY MOBILE
3	California	Part Time	RFTN-non exempt	Any Job	CALIFORNIA HOURLY
4	California	Full Time	RFTN-non exempt	Any Job	CA HOURLY BENEFITS
5	California	Temp	RFTN-non exempt	Any Job	CA HOURLY BENEFITS
6	Non-CA	Part Time	RFTN-non exempt	Data Associate	OTHER STATES HRLY MOBILE
7	Non-CA	Part Time	RFTN-non exempt	Any Job	OTHER STATES HOURLY
8	Non-CA	Full Time	RFTN-non exempt	Any Job	OTHER HOURLY BENEFITS
9	Non-CA	Temp	RFTN-non exempt	Any Job	OTHER HOURLY BENEFITS
10	Any State	Full Time	RFTE-exempt	Any Job	SALARY1
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IF(AND(B2="Full Time",C2="RFTE-exempt"),"SALARY1",IF(A2="California",IF(D2="Data Associate","CALIFORNIA HOURLY MOBILE",IF(B2="Part Time","CALIFORNIA HOURLY","CA HOURLY BENEFITS")),IF(D2="Data Associate","OTHER STATES HRLY MOBILE",IF(B2="PART TIME","OTHER STATES HOURLY","OTHER HOURLY BENEFITS"))))

 

[Rich1362]

This looks like it works

Book1
	A	B	C	D	E
1	Worked-In State	Worker Category Description	FLSA	Job Title	Pay class
2	California	Part Time	RFTN-non exempt	Data Associate	CALIFORNIA HOURLY MOBILE
3	California	Part Time	RFTN-non exempt	Any Job	CALIFORNIA HOURLY
4	California	Full Time	RFTN-non exempt	Any Job	CA HOURLY BENEFITS
5	California	Temp	RFTN-non exempt	Any Job	CA HOURLY BENEFITS
6	Non-CA	Part Time	RFTN-non exempt	Data Associate	OTHER STATES HRLY MOBILE
7	Non-CA	Part Time	RFTN-non exempt	Any Job	OTHER STATES HOURLY
8	Non-CA	Full Time	RFTN-non exempt	Any Job	OTHER HOURLY BENEFITS
9	Non-CA	Temp	RFTN-non exempt	Any Job	OTHER HOURLY BENEFITS
10	Any State	Full Time	RFTE-exempt	Any Job	SALARY1
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IF(AND(B2="Full Time",C2="RFTE-exempt"),"SALARY1",IF(A2="California",IF(D2="Data Associate","CALIFORNIA HOURLY MOBILE",IF(B2="Part Time","CALIFORNIA HOURLY","CA HOURLY BENEFITS")),IF(D2="Data Associate","OTHER STATES HRLY MOBILE",IF(B2="PART TIME","OTHER STATES HOURLY","OTHER HOURLY BENEFITS"))))

This is PERFECT! it works exactly how I need it to work. The highest of ALL High 5's mentally coming your way.

One more question, now I'm just being needy i know haha, I drag this formula down to cover and apply to 50 cells, if only 20 cells have data and the other 30 are blank, where would I put the (& "") so that the cell stays blank if no data to report.

I TRIED THIS:
I kept your formula and tried adding the following to the end of it but no success:

=IF(AND(B2="Full Time",C2="RFTE-exempt"),"SALARY1",IF(A2="California",IF(D2="Data Associate","CALIFORNIA HOURLY MOBILE",IF(B2="Part Time","CALIFORNIA HOURLY","CA HOURLY BENEFITS")),IF(D2="Data Associate","OTHER STATES HRLY MOBILE",IF(B2="PART TIME","OTHER STATES HOURLY","OTHER HOURLY BENEFITS"), IF(B2="",D2=""),""))))

But didn't work. Thanks for you help @jasonb75 and to whomever else jumps on this.

 

[jasonb75]

How about this method, which will only show a result if all 4 columns (A, B, C, and D) have been completed?

=IF(COUNTA(A2:D2)=4,IF(AND(B2="Full Time",C2="RFTE-exempt"),"SALARY1",IF(A2="California",IF(D2="Data Associate","CALIFORNIA HOURLY MOBILE",IF(B2="Part Time","CALIFORNIA HOURLY","CA HOURLY BENEFITS")),IF(D2="Data Associate","OTHER STATES HRLY MOBILE",IF(B2="PART TIME","OTHER STATES HOURLY","OTHER HOURLY BENEFITS")))),"")