Forum Discussion

DR_74's avatar
DR_74
Copper Contributor
Jun 14, 2024

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 exampl...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Jun 14, 2024

DR_74 

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,
        ""
    )
)
DR_74's avatar
DR_74
Copper Contributor
Jun 15, 2024

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;"")

 

Resources