This content is not currently supported on this browser.
+ Start a Discussion

Match values in 2 picklist to find the result

 I'm trying to compare values of two picklists to get the right value


For example if the cruise type is 'Half Day' and the Reseller is 'INP' the value is $16


I am using the following..... (excerpt only)


IF ((ISPICKVAL(RID__c,"Acorne")&&(ISPICKVAL(ET__c,"Half Day"))),16,
IF ((ISPICKVAL(RID__c,"Activity Gift/Maximise")&&(ISPICKVAL(ET__c,"Half Day"))),15,
IF ((ISPICKVAL(RID__c,"Activity Gift/Maximise")&&(ISPICKVAL(ET__c,"High Speed"))),20,
IF ((ISPICKVAL(RID__c,"Attraction World")&&(ISPICKVAL(ET__c,"Half Day"))),18,
IF ((ISPICKVAL(RID__c,"Buy a Gift")&&(ISPICKVAL(ET__c,"Half Day"))),12,
IF ((ISPICKVAL(RID__c,"Buy a Gift")&&(ISPICKVAL(ET__c,"Half Day for 2"))),6,
IF ((ISPICKVAL(RID__c,"Buy a Gift")&&(ISPICKVAL(ET__c,"High Speed"))), 7,




The formula works but there are so many picklist values that it is too big when it compiles


I'm trying to find a more effecient way of writing it so that it will compile and have tried 


IF (ISPICKVAL (ET__c,"Half Day"),CASE(RID__c,"Acorne",1,"Direct",2,0),
IF ((ISPICKVAL(ET__c, "High Speed"),CASE ((RID__c,"Acorne",1,"Direct",2,0))))



and also


CASE(ET__c,"Half Day", CASE(RID__c, "Acorne", 1, "Direct", 2, 0), "High Speed", CASE (RID__c, "Acorne", 5, "Direct", 10, 0)


but can't get the syntax right to make them work


Any suggestions?


Do you know how much over the max characters you are? I see a LOT of extra characters in your formula. Mostly just unnecessary parentheses. Also, remove the spaces (I see one after every "IF"). It appears you have about 2 extra characters per line you could remove. You could really lean this formula out that way.


You cannot use CASE() in this instance since you have two variables to consider.


Thanks xphiberoptikx for the feedback


I've removed the spaces and parentheses and that's reduced it, but I'm still over the limit, and it doesn't allow for any future additions that might come allow. 


Not sure if there is another way around it?

Take a look at the case method that might shorten it.
Additionally I noticed that you ask more than once about ET being equal to "half day" -
You can probably merge that and only ask once.



I couldnt think of how to get CASE() to work. Can you post a starting example for the OP (and me).



Steve MolisSteve Molis

I think you need to come up with a conversion chart for each list of picklist values then you could probably crack this thing with some basic math.  then you could do something kinda like this: 


"Direct", 1,
"Acorne", 2,
"Activity Gift/Maximise", 2.5,
"Attraction World", 3, 
"Buy a Gift",  3.5,
"Half Day",5,
"High Speed",10,