How to use multiple if condition in formula field? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Hardik ChavdaHardik Chavda 

How to use multiple if condition in formula field?

Following is the scenario.
if(industry == 'health' && type == 'Cost'){
  return 1;
}
else{
return 2;
}


if(industry == 'banking' && type == 'detail'){
return 3;
}
else{
return 4;
}

.
.
.
etc.

Is there any way I can use this multiple if in Formula field??
 
Best Answer chosen by Hardik Chavda
Vrajesh ShethVrajesh Sheth
Hi Hardik,

Create one field which will concat your field value (e.g. for TEXT(Industry) = "Health" and TEXT(Type) = "Cost", the value would be HealthCost). And then Use that field in Case statement.

Thanks
Vrajesh Sheth

All Answers

Sunil SarillaSunil Sarilla
Hi Hardik,
Yes, you can, and the syntax will be as below
IF(
AND(
TEXT(Industry) = "Health",
TEXT(Type) = "Cost"
   ),
"Return Value1",
IF(
AND(
TEXT(Industry) = "Banking",
TEXT(Type) = "Detail"
   ),
"Return Value2",
"Return Value3"
  )
)
Ben NightingaleBen Nightingale
You can write a formula like this. The salesforce formula will look to see of the first criteria is matched and display the result if it is and then if not then the else result is another If formula.

If( ispickval( industry,”health”) && ispickval (type, “Cost”),
   1,
If ( ispickval( industry,”banking”) && ispickval (type, “detail”),
   3,
   4
))

This is called a nested If statement. Because it is nested you need a criteria for each result so if you wated to have four results like in your question then you need to define a unique criteria for each one.

 
 
Hardik ChavdaHardik Chavda
Hi Sunil,

Thanks for the quick reply. Actually I already used this in formulla field but it's length got exceeded. Is there any more smarter way that we can use it in formula, like we can use CASE but in that I can use only one field while I have to check for 2 fields.
Sunil SarillaSunil Sarilla
you could try using CASE, could you provide us with all the combinations and its return values.
Vrajesh ShethVrajesh Sheth
Hi Hardik,

Create one field which will concat your field value (e.g. for TEXT(Industry) = "Health" and TEXT(Type) = "Cost", the value would be HealthCost). And then Use that field in Case statement.

Thanks
Vrajesh Sheth
This was selected as the best answer
Hardik ChavdaHardik Chavda
Hi Ben,

That's the exact thing I did. But my formula field is bit larger But if I can utilize it then it can fit inro formula field, So I want a bit smarter solution.
Sunil SarillaSunil Sarilla
Hi Hardik,
Try the below, replace the Industry Values with your Actuals Options for Industry picklist and same with the Type Values
CASE(
CASE(Industry,
"Industry Picklist Option1",2,
"Industry Picklist Option2",4,
"Industry Picklist Option3",6,
"Industry Picklist Option4",8,
"Industry Picklist Option5",10,
"Industry Picklist Option6",12,
"Industry Picklist Option7",14,
"Industry Picklist Option8",16,
"Industry Picklist Option9",18,
"Industry Picklist Option10",20,
"Industry Picklist Option11",22,
"Industry Picklist Option12",24,
"Industry Picklist Option13",26,
"Industry Picklist Option14",28,
"Industry Picklist Option15",30,
"Industry Picklist Option16",32,0)+
CASE(Type,
"Type Picklist Option1",3,
"Type Picklist Option2",6,
"Type Picklist Option3",7,
"Type Picklist Option4",9,
"Type Picklist Option5",12,
"Type Picklist Option6",15,
"Type Picklist Option7",18,0)
,
5,"Return value1",
8,"Return value2",
10,"Return value3",
"Return Value4"
)

If not the other alternative, create a custom field of Text return type and create a workflow on the Account Object and use a field update action to update the custom text field with  avlue that concatenates Industry and Type field.
Use this field in the formula i.e.
CASE(Customfield,"Option1","Value1",
"Option2","Value2",
"Value3"
)
 
Hardik ChavdaHardik Chavda
First of all, thanks to everyone. Sunil, your solution worked too but Vrajesh's solution was quicker and easier. I got my exact answer there. Thanks for putting your efforts. :)