Excel formula IF AND OR

Copper Contributor

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

DR_74_2-1718401284792.png

 

thank for the help

D

 

3 Replies

@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,
        ""
    )
)

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

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