If else formula field condition

In my object record type i have created formula field. Now i want my formula field to work as below.

If Basement__c = NA and Property__r.Basement__c = NA OR Basement__c = Crawl and Property__r.Basement__c = Crawl OR Basement__c = Slab and Property__r.Basement__c = Slab Then it will return Type__c(formula field) = "MAtch"

And (Another rule) Basement__c DOES NOT contain any of the following "N/A,Crawl,Slab,None and Property__r.Basement__c Does not contain any of this then formula field will return "HAs basement"

• Are these 2 different formula fields, or you can have it combined in one? Also, what N/A value do you have in your picklists - "N/A", "NA", or ""(blank)? Commented Apr 20, 2016 at 16:19

First formula:

``````IF(
CASE( Basement__c, "N/A",1, "Crawl",2, "Slab",3,4) =
CASE( Property__r.Basement__c, "N/A",1, "Crawl",2, "Slab",3,5),
"Match",
"No Match"
)
``````

Optimizations used here:

Since you're asking the two fields to be the same, that reduces the complexity of the formula. Next, we use CASE to remove most of the field references.

Second Formula:

``````IF(
NOT(
OR(
CASE( Basement__c, "N/A",1,  "Crawl",1, "Slab",1, "None",1, 0)=1,
CASE( Property__r.Basement__c, "N/A",1,  "Crawl",1, "Slab",1, "None",1, 0)=1
)
),
"Has Basement",
"Does not have basement"
)
``````

Optimizations used here:

De Morgan's Law allows us to transform (NOT A AND NOT B) to simply "NOT (A OR B)". Again, we use CASE to reduce the number of field references used here.

• And this is picklist field, So do i need to add like PICKLIST( REOHQ__REOHQ_Basement__c = 'NA' ? Commented Apr 20, 2016 at 15:56
• @Salesfor_dev No, CASE supports picklist values directly. Just make sure the values are correct (you used NA sometimes and N/A other times).
– sfdcfox
Commented Apr 20, 2016 at 15:58
• Ok thanks, And i want to use this both formula in one formula field. So is there any way i can apply this both formula on one field? Commented Apr 20, 2016 at 16:00
• Sure, you can concatenate them together using text concatenation operator &: `IF(...) & IF(...)`.
– sfdcfox
Commented Apr 20, 2016 at 16:01
• Tried that but getting picklist error like, Field Basement__c is a picklist field. Picklist fields are only supported in certain function Commented Apr 20, 2016 at 16:06

this will get what you wanted: ```IF( AND( CONTAINS("N/A:Crawl:Slab",TEXT(Basement__c)), Basement__c = Property__r.Basement__c ), "Match", IF( OR( CONTAINS("N/A:Crawl:Slab:None",TEXT(Basement__c)), CONTAINS("N/A:Crawl:Slab:None",TEXT(Property__r.Basement__c)) ) "", "Has Basement" ) )```