If else formula field condition

Question

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"

How can i achieve this? Please help.

Answer 1

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.

Answer 2

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" ) )