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.

 

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

Resources