Hello,

Need some help in using the IF statement.

I have two columns A & B
A has 4 types of words:
RoHS
RoHS5
RoHS6
No

Column B has:
Yes
No

I need the following output in three separate columns: X, Y, Z

I want to check if A1 = RoHS and B1 =yes....X1= compliant
If A1=RoHS and B1=no...X1=compliant and Y1= does not meet
or If A1=RoHS6 and B1 = no...... X1=compliant and Y1= does not meet
If A1=RoHS6 and If B1= yes, X1=compliant and Y1=check
If A1=RoHS5 ....X1=exempt, Z2=7b (column B does not matter in this case)
If A1=No....X1=non compliant

Can anybody help me if it makes sense.

thanks

"Kinggops" wrote:
Need some help in using the IF statement.

A more elegant and flexible approach might be to set up
lookup tables for X, Y and Z. But as you wish ....

X1: =IF(A1="RoHS5","Exempt",IF(A1="No","Non-Compliant",
IF(AND(A1="RoHS",B1<"No"),"","Compliant")))

Note: Did you really mean to omit the case for A1=RoHS
and B1=yes? If you want something other than blank,
replace "" above or simplify the last condition. I suspect
you want X1=compliant, in which case omit the 3rd IF()
and change the 2nd IF() to
IF(A1="No","Non-Compliant","Compliant").

Y1: =IF(A1="RoHS6","Check",
IF(AND(B1="No",OR(A1="RoHS",A1="RoHS6")),"Does Not Meet",""))

Z1: =IF(A1="RoHS5","7b","")


----- full posting -----

"Kinggops" wrote:
Hello,

Need some help in using the IF statement.

I have two columns A & B
A has 4 types of words:
RoHS
RoHS5
RoHS6
No

Column B has:
Yes
No

I need the following output in three separate columns: X, Y, Z

I want to check if A1 = RoHS and B1 =yes....X1= compliant
If A1=RoHS and B1=no...X1=compliant and Y1= does not meet
or If A1=RoHS6 and B1 = no...... X1=compliant and Y1= does not meet
If A1=RoHS6 and If B1= yes, X1=compliant and Y1=check
If A1=RoHS5 ....X1=exempt, Z2=7b (column B does not matter in this case)
If A1=No....X1=non compliant

Can anybody help me if it makes sense.

thanks

It took a bit but here ya go:

Enter this formula into X1

=IF(AND(OR(A1="RoHS",A1="RoHS6"),OR(B1="Yes",B1="N o")),"compliant",IF(A1="RoHS5","exempt",IF(A1="No" ,"non
compliant","")))

Enter this formula into Y1

=IF(AND(OR(A1="RoHS",A1="RoHS6"),B1="No"),"does not
meet",IF(AND(A1="RoHs6",B1="Yes"),"check",""))

Enter this formula into Z 2

=IF(AND(A1="RoHs5",X1="exempt"),B7,"")

Ya gotta love boolean operators!!!!!

Learnt something today.Thanks a great deal!

" wrote:

It took a bit but here ya go:

Enter this formula into X1

=IF(AND(OR(A1="RoHS",A1="RoHS6"),OR(B1="Yes",B1="N o")),"compliant",IF(A1="RoHS5","exempt",IF(A1="No" ,"non
compliant","")))

Enter this formula into Y1

=IF(AND(OR(A1="RoHS",A1="RoHS6"),B1="No"),"does not
meet",IF(AND(A1="RoHs6",B1="Yes"),"check",""))

Enter this formula into Z 2

=IF(AND(A1="RoHs5",X1="exempt"),B7,"")

Ya gotta love boolean operators!!!!!

It works! Thank you for the help. Can you please tell me how this could be
made more flexible using the lookup tables. thanks...

" wrote:

"Kinggops" wrote:
Need some help in using the IF statement.

A more elegant and flexible approach might be to set up
lookup tables for X, Y and Z. But as you wish ....

X1: =IF(A1="RoHS5","Exempt",IF(A1="No","Non-Compliant",
IF(AND(A1="RoHS",B1<"No"),"","Compliant")))

Note: Did you really mean to omit the case for A1=RoHS
and B1=yes? If you want something other than blank,
replace "" above or simplify the last condition. I suspect
you want X1=compliant, in which case omit the 3rd IF()
and change the 2nd IF() to
IF(A1="No","Non-Compliant","Compliant").

Y1: =IF(A1="RoHS6","Check",
IF(AND(B1="No",OR(A1="RoHS",A1="RoHS6")),"Does Not Meet",""))

Z1: =IF(A1="RoHS5","7b","")


----- full posting -----

"Kinggops" wrote:
Hello,

Need some help in using the IF statement.

I have two columns A & B
A has 4 types of words:
RoHS
RoHS5
RoHS6
No

Column B has:
Yes
No

I need the following output in three separate columns: X, Y, Z

I want to check if A1 = RoHS and B1 =yes....X1= compliant
If A1=RoHS and B1=no...X1=compliant and Y1= does not meet
or If A1=RoHS6 and B1 = no...... X1=compliant and Y1= does not meet
If A1=RoHS6 and If B1= yes, X1=compliant and Y1=check
If A1=RoHS5 ....X1=exempt, Z2=7b (column B does not matter in this case)
If A1=No....X1=non compliant

Can anybody help me if it makes sense.

thanks