ShowAll Questionssorted byDate Posted
petec@i2isys

# formula to evaluate two pick lists

Hello,
I would like to create a formula text field that would give me a value based on evaluation two different pick lists.

Example

If PicklistA is X and Picklist B is Y, then give me the value P1,
If PicklistA is X and PicklistB is Z, then give me value P2,
If PicklistA is W and PicklistB is T, then give me value P3,
and on it on until I account for all 16 different possibilites.

What would be the formula structure for this?
Thanks!
PulaK_PrabhakaR
Suppose "PicklistA" contains 4 values naming A, B, C and D. And "PicklistB" contains 4 values naming W, X, Y, and Z.
Create formula field(Text) with given formula, it will give output as per selected combinations in both picklists.

IF( ISPICKVAL( PicklistA__c , 'A')  &&  ISPICKVAL( PicklistB__c , 'W') , 'AW',
IF( ISPICKVAL( PicklistA__c , 'A')  &&  ISPICKVAL( PicklistB__c , 'X') , 'AX',
IF( ISPICKVAL( PicklistA__c , 'A')  &&  ISPICKVAL( PicklistB__c , 'Y') , 'AY',
IF( ISPICKVAL( PicklistA__c , 'A')  &&  ISPICKVAL( PicklistB__c , 'Z') , 'AZ',
IF( ISPICKVAL( PicklistA__c , 'B')  &&  ISPICKVAL( PicklistB__c , 'W') , 'BW',
IF( ISPICKVAL( PicklistA__c , 'B')  &&  ISPICKVAL( PicklistB__c , 'X') , 'BX',
IF( ISPICKVAL( PicklistA__c , 'B')  &&  ISPICKVAL( PicklistB__c , 'Y') , 'BY',
IF( ISPICKVAL( PicklistA__c , 'B')  &&  ISPICKVAL( PicklistB__c , 'Z') , 'BZ',
IF( ISPICKVAL( PicklistA__c , 'C')  &&  ISPICKVAL( PicklistB__c , 'W') , 'CW',
IF( ISPICKVAL( PicklistA__c , 'C')  &&  ISPICKVAL( PicklistB__c , 'X') , 'CX',
IF( ISPICKVAL( PicklistA__c , 'C')  &&  ISPICKVAL( PicklistB__c , 'Y') , 'CY',
IF( ISPICKVAL( PicklistA__c , 'C')  &&  ISPICKVAL( PicklistB__c , 'Z') , 'CZ',
IF( ISPICKVAL( PicklistA__c , 'D')  &&  ISPICKVAL( PicklistB__c , 'W') , 'DW',
IF( ISPICKVAL( PicklistA__c , 'D')  &&  ISPICKVAL( PicklistB__c , 'X') , 'DX',
IF( ISPICKVAL( PicklistA__c , 'D')  &&  ISPICKVAL( PicklistB__c , 'Y') , 'DY',
IF( ISPICKVAL( PicklistA__c , 'D')  &&  ISPICKVAL( PicklistB__c , 'Z') , 'DZ','Please Enter value in Both Picklist'))))))))))))))))

PulaK_PrabhakaR
Suppose "PicklistA" contains 4 values naming A, B, C and D. And "PicklistB" contains 4 values naming W, X, Y, and Z.
Create formula field(Text) with given formula, it will give output as per selected combinations in both picklists.

IF( ISPICKVAL( PicklistA__c , 'A')  &&  ISPICKVAL( PicklistB__c , 'W') , 'AW',
IF( ISPICKVAL( PicklistA__c , 'A')  &&  ISPICKVAL( PicklistB__c , 'X') , 'AX',
IF( ISPICKVAL( PicklistA__c , 'A')  &&  ISPICKVAL( PicklistB__c , 'Y') , 'AY',
IF( ISPICKVAL( PicklistA__c , 'A')  &&  ISPICKVAL( PicklistB__c , 'Z') , 'AZ',
IF( ISPICKVAL( PicklistA__c , 'B')  &&  ISPICKVAL( PicklistB__c , 'W') , 'BW',
IF( ISPICKVAL( PicklistA__c , 'B')  &&  ISPICKVAL( PicklistB__c , 'X') , 'BX',
IF( ISPICKVAL( PicklistA__c , 'B')  &&  ISPICKVAL( PicklistB__c , 'Y') , 'BY',
IF( ISPICKVAL( PicklistA__c , 'B')  &&  ISPICKVAL( PicklistB__c , 'Z') , 'BZ',
IF( ISPICKVAL( PicklistA__c , 'C')  &&  ISPICKVAL( PicklistB__c , 'W') , 'CW',
IF( ISPICKVAL( PicklistA__c , 'C')  &&  ISPICKVAL( PicklistB__c , 'X') , 'CX',
IF( ISPICKVAL( PicklistA__c , 'C')  &&  ISPICKVAL( PicklistB__c , 'Y') , 'CY',
IF( ISPICKVAL( PicklistA__c , 'C')  &&  ISPICKVAL( PicklistB__c , 'Z') , 'CZ',
IF( ISPICKVAL( PicklistA__c , 'D')  &&  ISPICKVAL( PicklistB__c , 'W') , 'DW',
IF( ISPICKVAL( PicklistA__c , 'D')  &&  ISPICKVAL( PicklistB__c , 'X') , 'DX',
IF( ISPICKVAL( PicklistA__c , 'D')  &&  ISPICKVAL( PicklistB__c , 'Y') , 'DY',
IF( ISPICKVAL( PicklistA__c , 'D')  &&  ISPICKVAL( PicklistB__c , 'Z') , 'DZ','Please Enter value in Both Picklist'))))))))))))))))
This was selected as the best answer
petec@i2isys
Hello Pulak,

That worked PERFECTLY, Thank you so much!

Pete
PulaK_PrabhakaR
Hi Pete, Please mark the answer as best answer if the information is informative.so that question is removed from an unanswered question and appear as a proper solution. PulaK PrabhakaR