Forum Discussion

mrcarlyoungblood's avatar
mrcarlyoungblood
Copper Contributor
Oct 04, 2024
Solved

Need help with this function statement. Not sure if I can use a if(or, or a if(and(or

=IF(J2="CEL",N2+30,IF(J2="AZA",N2+30,IF(J2="CUN",N2+30,IF(J2="LIN",L2+60,IF(J2="EXP",L2+60,IF(J2="CYL",L2+30,IF(J2="GLO",L2+30,IF(J2="MSC",L2+30,IF(J2="SIL",L2+30,IF(J2="TAU",L2+30,IF(L2="SHL",L2+45,...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Oct 04, 2024

    mrcarlyoungblood 

    =IF(J2="CEL",N2+30,

    IF(J2="AZA",N2+30,

    IF(J2="CUN",N2+30,

    IF(J2="LIN",L2+60,

    IF(J2="EXP",L2+60,

    IF(J2="CYL",L2+30,

    IF(J2="GLO",L2+30,

    IF(J2="MSC",L2+30,

    IF(J2="SIL",L2+30,

    IF(J2="TAU",L2+30,

    IF(L2="SHL",L2+45,

    IF(J2="SCE",L2+45,

    IF(J2="SEA",N2+45,

    IF(J2="NOR",N2+14))))))))))))))

     

    With OR you can use the below formula.

     

    =IF(OR(J2="CEL",J2="AZA",J2="CUN"),N2+30,

    IF(OR(J2="LIN",J2="EXP"),L2+60,

    IF(OR(J2="CYL",J2="GLO",J2="MSC",J2="SIL",J2="TAU"),L2+30,

    IF(OR(L2="SHL",J2="SCE"),L2+45,

    IF(J2="SEA",N2+45,

    IF(J2="NOR",N2+14))))))

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 04, 2024

mrcarlyoungblood 

One variant as OliverScheurich  suggested

=IF( OR( J2 = {"CEL","AZA","CUN"} ),       N2+30,
 IF( OR( J2 = {"LIN","EXP"} ),             L2+60,
 IF( OR( J2 = {"GLO","MSC","SIL","TAU"} ), L2+30,
 IF( OR( J2 = {"SHL","SCE"} ),             L2+45,
 IF(     J2 =  "SEA",                      N2+45,
 IF(     J2 =  "NOR",                      N2+14
))))))

Another variant create the table tbl like

and use formula

=LET(
  l, XLOOKUP(J2, tbl[J], tbl[L]),
  n, XLOOKUP(J2, tbl[J], tbl[N]),
  IF(l, L2+l, N2+n)
)

Resources