+ Start a Discussion
Chris McKaughanChris McKaughan 

Need help with IF AND formula

Hello,

I'm trying to set a currency field with a price based on a size code text field and a pricing tier number field. Can't seem to get the syntax right. Any thoughts?

Thanks,
Chris

IF(AND( Size_Code__c= "S"), Tier__c= 1, 595,
IF(AND( Size_Code__c= "S"), Tier__c= 2, 655,
IF(AND( Size_Code__c= "S"), Tier__c= 3, 945,
IF(AND( Size_Code__c= "M"), Tier__c= 1, 835,
IF(AND( Size_Code__c= "M"), Tier__c= 2, 945,
IF(AND( Size_Code__c= "M"), Tier__c= 3, 1405,
IF(AND( Size_Code__c= "L"), Tier__c= 1, 1125,
IF(AND( Size_Code__c= "L"), Tier__c= 2, 1305,
IF(AND( Size_Code__c= "L"), Tier__c= 3, 2025,
IF(AND( Size_Code__c= "XL"), Tier__c= 1, 1555,
IF(AND( Size_Code__c= "XL"), Tier__c= 2, 1835,
IF(AND( Size_Code__c= "XL"), Tier__c= 3, 2910,
IF(AND( Size_Code__c= "XXL"), Tier__c= 1, 2225,
IF(AND( Size_Code__c= "XXL"), Tier__c= 2, 2385,
IF(AND( Size_Code__c= "XXL"), Tier__c= 3, 4525,
0)))))))))))))))
Best Answer chosen by Chris McKaughan
Nayana KNayana K
CASE(Size_Code__c,
"S", CASE(Tier__c, 
             1, 595,
             2,655,
             3, 945,
             0),
"M", CASE(Tier__c, 
             1, 835,
             2,835,
             3, 1405,
             0),
"L", CASE(Tier__c, 
             1, 1125,
             2,1305,
             3, 2025,
             NULL),
"XL", CASE(Tier__c, 
             1, 1555,
             2,1835,
             3, 2910,
             0),
"XXL", CASE(Tier__c, 
             1, 2225,
             2,2385,
             3, 4525,
             0),
0)

All Answers

Nayana KNayana K
CASE(Size_Code__c,
"S", CASE(Tier__c, 
             1, 595,
             2,655,
             3, 945,
             0),
"M", CASE(Tier__c, 
             1, 835,
             2,835,
             3, 1405,
             0),
"L", CASE(Tier__c, 
             1, 1125,
             2,1305,
             3, 2025,
             NULL),
"XL", CASE(Tier__c, 
             1, 1555,
             2,1835,
             3, 2910,
             0),
"XXL", CASE(Tier__c, 
             1, 2225,
             2,2385,
             3, 4525,
             0),
0)
This was selected as the best answer
Chris McKaughanChris McKaughan
Thanks Nayana! This is great! Much appreciated!
Nayana KNayana K
Please mark this as solved