Forum Discussion
How do I create an formula containing IFs, OR ANDs and lists?
The formula is OK. I'd use ISNUMBER and MATCH for the range L6:L7 too; that makes it easier to add Cost Centers if necessary.
So:
=ROUND(IF(AND(F$2=$D3,$E3="sz"),$C3*$J$2,IF(AND(F$2=$D3,OR(ISNUMBER(MATCH($A3,$J$7:$J$13,0)),ISNUMBER(MATCH($B3,$L$6:$L$7,0)))),$C3,0)),0)
or, slightly shorter:
=ROUND(IF(F$2=$D3,$C3*IF($E3="sz",$J$2,IF(OR(ISNUMBER(MATCH($A3,$J$7:$J$13,0)),ISNUMBER(MATCH($B3,$L$6:$L$7,0))),1,0)),0),0)
Like this:
=IF(AND(G2=$E3,ISNUMBER(MATCH(C3,$O$3:$O$12,0))),$D3,0)
You can expand the range $O$3:$O$12 as needed.
Thank you so much for this, but can I ask you one last question. Is this the best / quickest formula to use ?
The formula is OK. I'd use ISNUMBER and MATCH for the range L6:L7 too; that makes it easier to add Cost Centers if necessary.
So:
=ROUND(IF(AND(F$2=$D3,$E3="sz"),$C3*$J$2,IF(AND(F$2=$D3,OR(ISNUMBER(MATCH($A3,$J$7:$J$13,0)),ISNUMBER(MATCH($B3,$L$6:$L$7,0)))),$C3,0)),0)
or, slightly shorter:
=ROUND(IF(F$2=$D3,$C3*IF($E3="sz",$J$2,IF(OR(ISNUMBER(MATCH($A3,$J$7:$J$13,0)),ISNUMBER(MATCH($B3,$L$6:$L$7,0))),1,0)),0),0)
- Thank you SO much this really has helped me no end 🙂
- Thank you so much for this, its much easier and I have learnt something new )
