Formula Field - Case Function

Question

Am trying to build a formula field with multiple IF conditions, so decided to go with Case function, below is the return statement based on the picklist value

0  Member
2  Member
3  Member
22 Only a Member if the "Date__c" field is not blank, otherwise Non-Member
23 Non-Member
27 Only a Member if the "Date__c" field is not blank, otherwise Non-Member
70 Only a Member if the "Date__c" field is not blank, otherwise Non-Member
80 Only a Member if the "Date__c" field is not blank, otherwise Non-Member
85 Non-Member
87 Only a Member if the "Date__c" field is not blank, otherwise Non-Member
90 Only a Member if the "Date__c" field is not blank, otherwise Non-Member
99 Member

So I created something like

CASE(TEXT(MDPKG__Status_Code__c),
'00', 'Member',
'02', 'Member',
'03', 'Member',
'99', 'Member',
'23', 'Non-Member',
'85', 'Non-Member',''
)

Couldn't Find a way to check those If conditions inside case function (Is it possible?) Should I use different function ?

Answer 1

If function will work inside the case statement in formula

You can do like this:-

CASE( sa_nket__case_text__c , 
'00', 'Member', 
'02', 'Member', 
'03', 'Member', 
'99', 'Member', 
'23', IF( ISBLANK( sa_nket__text_field__c ) , 'Memeber', 'Non-Member'), 
'85', 'Non-Member','' 
)

Note:-

CASE functions cannot contain functions that return true or false. Instead, make true or false expressions return numbers such as:

CASE(1, IF(ISPICKVAL​ (Term__c, "12"),​ 1, 0),
 12 * Monthly_Commit__c,​​
 IF(ISPICKVAL(Term__c, "24"), 1, 0),​​
 24 * Monthly_Commit__c, 0)

Make your changes accordingly. Hope it helps you.

Answer 2

"CASE functions cannot contain functions that return true or false"

For anyone reading this, it is correct, however there is a workaround: compare your CASE() formula with the number 1 to make the result pass (true) or fail (false)

CASE() = 1

This helped me in a formula.