This is a job for the CASE() function. Chaining a bunch of IF()s together would work, but that would be painful. You could reduce typing by grouping the tests for various picklist values together, but CASE() is going to be much less painful to write (and read).
Before I get to that though, let's examine why your current formula is running into errors.
First, you're not using the IF() function correctly
If you look at Formula Operators and Functions (a very good thing to reference while building formulas), you'll see that IF() looks like this
IF(logical_test, value_if_true, value_if_false)
Changing things around just a bit so that we end up conforming to best practice (each statement on its own line, indent one extra level when you encounter an open parenthesis)
IF(
logical test,
value_if_true,
value_if_false
)
It takes 3 parameters, all of which are required, and they need to be in that order (separated by a comma).
If we try to format your formula in that manner, it'll look like
IF(
OR(
(ISPICKVAL(Package__c, "A")),
"Yes",
IF(
OR(
(ISPICKVAL(Package__c, "B")),
"Yes",
/* I'm not typing out the rest */
You never end up having a closing parenthesis for any of your IF(), and everything is smashed into the logical_test. Here, you want
- your "logical test" to be "has the user selected value "A" in this picklist
- your "value if true" to be "yes"
- and your "value if false" to be a nested
IF() which returns either "Yes" or "No"
Being mindful of parenthesis and keeping the above in mind, you can shuffle things around a bit to get closer to a proper formula
IF(
/* This OR() is our logical test */
/* The open paren needs a matching close paren*/
OR(
/* No need for the extra parens surrounding ISPICKVAL() */
ISPICKVAL(Package__c, "A")
),
"Yes",
/* We can nest IF() functions inside of an IF() function */
/* This gives us an if-else if, which can be a useful construction */
IF(
OR(
ISPICKVAL(Package__c, "B")
),
"Yes",
/* I'm still not typing out the rest */
Second, you're not using the OR() function correctly
OR() takes one or more boolean values, and returns true if any of them are true. Working out the proper usage of IF() kinda sorted this issue for us as well, but OR(ISPICKVAL(Package__c, "B"), "Yes", IF(...)) is not correct because "Yes" is not a boolean value, and the nested IF() doesn't return a boolean value either.
You can use an OR() with only one value, but that doesn't make too much sense. In this case, the OR()s can be completely removed. In general, the less typing we do, the less likely it is for us to make mistakes.
IF(
/* ISPICKVAL() returns true/false, so it is suitable for our */
/* logical test all on its own */
ISPICKVAL(Package__c, "A"),
"Yes",
IF(
ISPICKVAL(Package__c, "B"),
"Yes",
/* I'm still not typing out the rest */
)
We're getting closer. This would work, but it could still be improved further.
Third, your composition of IF() and OR() is incorrect
I get the feeling that you were trying to turn natural language into a formula. Something like
If package is A, then yes, or if package is B, then yes, or if package is C...
Writing out, in plain language, what you intend your formula to do is definitely a helpful tool for planning/organizing a formula. You do need to kinda stick close to how formulas work though, and that's where I think your attempt fell apart. It's something that takes a bit of practice, but isn't too hard once you gain some familiarity with formulas.
I would write this in plain language like so
If package is A, then yes, else if package is B, then yes, else if package is C...
That would lead you to the nested IF() approach, but it seems like we're repeating ourselves quite a bit. While we can only compare a field to a single value at a time, we can reintroduce the OR() and group all of the comparisons that should result in "yes" into a single logical test
If package is A or package is B or package is C, then yes, else...
Turning that into a formula
IF(
OR(
ISPICKVAL(Package__c, "A"),
ISPICKVAL(Package__c, "B"),
ISPICKVAL(Package__c, "C"),
ISPICKVAL(Package__c, "D"),
ISPICKVAL(Package__c, "E"),
ISPICKVAL(Package__c, "F")
),
"Yes",
IF(
OR(
ISPICKVAL(Package__c, "G"),
ISPICKVAL(Package__c, "H"),
ISPICKVAL(Package__c, "I")
),
"No",
"Invalid value"
)
)
That's much more compact (if we ignore the extra spacing that we're using for formatting purposes), and much less repetition.
If you wanted to use IF(), this is how you would want to do it
One point I've glossed over a bit
Is that formulas require parenthesis to be balanced. For every open parenthesis, there must be a matching closing parenthesis.
There can end up being a lot of parenthesis, which is really hard for our brains to track. By formatting your formula according to best practice, it makes it really easy to see when (and where) you've made a mistake.
compare
IF(field__c = 1, "A", IF(field__c = 2, "B", IF(field__c = 3, "C", IF(field__c = 4, "D", IF(field__c = 5, "E", "F")))
to
IF(
field__c = 1,
"A",
IF(
field__c = 2,
"B",
IF(
field__c = 3,
"C",
IF(
field__c = 4,
"D",
IF(
field__c = 5,
"E",
"F"
)
)
)
The second one is much easier to see that you've missed some closing parenthesis (specifically, 2 are missing).
But we can still do better with CASE()
CASE() is one of the few functions that can work directly on picklists, so you don't need ISPICKVAL() with it. It also means that you don't have to repeat writing Package__c multiple times.
Its usage, with formatting, is like so
CASE(
field,
value 1, result 1,
value 2, result 2,
...
value n, result n,
default result (if none of the values matched)
)
The minimum number of parameters is 4 (field, one value and result, the default). In your case
CASE(
Package__c,
"A", "Yes",
"B", "Yes",
"C", "Yes",
"D", "Yes",
"E", "Yes",
"F", "Yes",
"G", "No",
"H", "No",
"I", "No",
"Invalid value"
)
