Forum Discussion
Excel formula IF AND OR
Hello,
I am trying to use IF AND OR to have some value from column A copied in the columns H / I / J / K / L / M with different conditions of data in columns B/C/D/E/F/G
in the image some example of data and result with the formula I have tried, in red strikethrough value that should not be returned and in orange value that are not copied from column A
thank for the help
D
3 Replies
In J2:
=IF( OR( $D2="Approved", $D2="Approved as noted", $D2="Suitable for Tender" ), "", IF( OR( $C2="Approved", $C2="Approved as noted", $C2="Suitable for Tender" ), $A2, "" ) )HansVogelaar thanks for the reply,
in the excel attached to my firs message there are some formula that I have tried to use to have the following condition and is nearly working except for some error:
e.g. when I have B/C/D not "Approved";"Approved as noted";"Suitable for Tender" but E/F/G not blank J = "FALSE" instead of blank
e.g. when I have B not "Approved";"Approved as noted";"Suitable for Tender" but C/D "Approved";"Approved as noted";"Suitable for Tender" and E/F/G empty K = A2 and L = A2 instead of empty
below formulas that I have used:
Column H:
if B/C/D are not "Approved";"Approved as noted";"Suitable for Tender" and E/F/G are blank H2 = A2
but if C or D are "Approved";"Approved as noted";"Suitable for Tender" or E/F/G are not blank H2 = empty
=IF(OR($B2="Approved";$B2="Approved as noted";$B2="Suitable for Tender");"";
IF(AND($B2<>"Approved";$B2<>"Approved as noted";$B2<>"Suitable for Tender";$C2<>"Approved";
$C2<>"Approved as noted";$C2<>"Suitable for Tender";$D2<>"Approved";$D2<>"Approved as noted";$D2<>"Suitable for Tender";$E2="";$F2="";$G2="");$A2;""))Column I:
if B is "Approved";"Approved as noted";"Suitable for Tender" and C/D are not "Approved";"Approved as noted";"Suitable for Tender" and E/F/G are blank I2 = A2
but if C or D are "Approved";"Approved as noted";"Suitable for Tender" or E/F/G are not blank I2 = empty
=IF(OR($C2="Approved";$C2="Approved as noted";$C2="Suitable for Tender");"";
IF(OR($B2="Approved";$B2="Approved as noted";$B2="Suitable for Tender");$A2;
IF(AND($D2<>"Approved";$D2<>"Approved as noted";$D2<>"Suitable for Tender";$E2="";$F2="";$G2="";$B2<>"Approved";$B2<>"Approved as noted";$B2<>"Suitable for Tender";);$A2;"")))
Column J:
if C is "Approved";"Approved as noted";"Suitable for Tender" and D is not "Approved";"Approved as noted";"Suitable for Tender" and E/F/G are blank I2 = A2
but if C or D are "Approved";"Approved as noted";"Suitable for Tender" or E/F/G are not blank J2 = empty
=IF(OR($D2="Approved";$D2="Approved as noted";$D2="Suitable for Tender");"";
IF(OR($C2="Approved";$C2="Approved as noted";$C2="Suitable for Tender");$A2;
IF(AND($E2="";$F2="";$G2="");"")))
Column K:
if D is "Approved";"Approved as noted";"Suitable for Tender" and E/F/G are blank K2 = A2
but if D is "Approved";"Approved as noted";"Suitable for Tender" or E/F/G are not blank K2 = empty
=IF(AND($E2="";OR($B2="Approved";$B2="Approved as noted";$B2="Suitable for Tender");OR($C2="Approved";$C2="Approved as noted";$C2="Suitable for Tender"););"";
IF($F2<>"";"";
IF(AND($D2<>"Approved";$D2<>"Approved as noted";$D2<>"Suitable for Tender");"";
IF(AND($E2="";$G2="");$A2;""))))
Column L:
E/is not blank and F/G are blank L2 = A2
but if F/G are not blank L2 = empty
=IF(AND($F2="";$E2="";OR($B2="Approved";$B2="Approved as noted";$B2="Suitable for Tender");OR($C2="Approved";$C2="Approved as noted";$C2="Suitable for Tender");OR(
$D2="Approved";$D2="Approved as noted";$D2="Suitable for Tender"));"";IF($F2<>"";"";
IF(AND($D2<>"Approved";$D2<>"Approved as noted";$D2<>"Suitable for Tender");"";
IF(AND($F2="";$G2="");$A2;""))))Column M:
=IF($F2<>"";$A2;"")
HansVogelaar thanks for the reply.
I have different formula in the columns on which the value from A2 should be copied if in the column B/C/D/E/F/G i have some specific value, and now is working almost for all except:
when B/C/D/ not contain "Approved"; "Approved as noted"; "Suitable for Tender" and E is not blank in J I have FALSE instead of empty
and is not working when I have in D "Approved"; "Approved as noted"; "Suitable for Tender" the value from A2 should be only in K but now is copied in K and L.
I hope is clear what I am trying to do, in the excel I have attached there are the formulas I have used.
column H:
if B/C/D/ not contain "Approved"; "Approved as noted"; "Suitable for Tender" and E/F/G are empty (no dates) column H = A2.
but if B or one of the column after (C/D or E/F/G) contain the specific value or date H should be empty
=IF(OR($B2="Approved";$B2="Approved as noted";$B2="Suitable for Tender");"";
IF(AND($B2<>"Approved";$B2<>"Approved as noted";$B2<>"Suitable for Tender";$C2<>"Approved";
$C2<>"Approved as noted";$C2<>"Suitable for Tender";$D2<>"Approved";$D2<>"Approved as noted";$D2<>"Suitable for Tender";$E2="";$F2="";$G2="");$A2;""))column I:
If C contain "Approved"; "Approved as noted"; "Suitable for Tender" and D/ not contain "Approved"; "Approved as noted"; "Suitable for Tender" and E/F/G are empty (no dates) column I = A2.
but if or one of the column after (D or E/F/G) contain the specific value or date I should be empty
=IF(OR($C2="Approved";$C2="Approved as noted";$C2="Suitable for Tender");"";
IF(OR($B2="Approved";$B2="Approved as noted";$B2="Suitable for Tender");$A2;
IF(AND($D2<>"Approved";$D2<>"Approved as noted";$D2<>"Suitable for Tender";$E2="";$F2="";
$G2="";$B2<>"Approved";$B2<>"Approved as noted";$B2<>"Suitable for Tender";);$A2;"")))column J:
If D contain "Approved"; "Approved as noted"; "Suitable for Tender" and E/F/G are empty (no dates) column J = A2.
but if or one of the column after (E/F/G) contain the specific value or date J should be empty
=IF(OR($D2="Approved";$D2="Approved as noted";$D2="Suitable for Tender");"";
IF(OR($C2="Approved";$C2="Approved as noted";$C2="Suitable for Tender");$A2;
IF(AND($E2="";$F2="";$G2="");"")))
column K:
If E is empty and /F/G are empty (no dates) column K = A2.
but if or one of the column after (/F/G) contain the specific value or date K should be empty
=IF(AND($E2="";OR($B2="Approved";$B2="Approved as noted";$B2="Suitable for Tender");
OR($C2="Approved";$C2="Approved as noted";$C2="Suitable for Tender"););"";
IF($F2<>"";"";IF(AND($D2<>"Approved";$D2<>"Approved as noted";$D2<>"Suitable for Tender");"";
IF(AND($E2="";$G2="");$A2;""))))column L
If F and G are empty (no dates) column L = A2.
but if or one of the column after (G) contain the specific value or date L should be empty
=IF(AND($F2="";$E2="";OR($B2="Approved";$B2="Approved as noted";$B2="Suitable for Tender");
OR($C2="Approved";$C2="Approved as noted";$C2="Suitable for Tender");
OR($D2="Approved";$D2="Approved as noted";$D2="Suitable for Tender"));"";
IF($F2<>"";"";IF(AND($D2<>"Approved";$D2<>"Approved as noted";$D2<>"Suitable for Tender");"";
IF(AND($F2="";$G2="");$A2;""))))column M:
If F is not empty column M = A2.
=IF($F2<>"";$A2;"")
