- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 06, 2020 02:09 PM
Hi everyone! First time posting. Appreciate your help!
I need your help in writing a formula that operates like this:
(Assume CATEGORY is a single select field.)
If CATEGORY = A, B, C, or D, then X
If CATEGORY = E, F, G, or H, then Y
If CATEGORY = I, J, K, OR L, then Z
I think a SWITCH formula would do the trick, but I’d like the result (X, Y or Z) to come from a single select field.
What I want to avoid is having to type out X, Y or Z multiple times. For example, here’s what I have now, which works but is not efficient:
SWITCH(
{CATEGORY},
“A”, “X”,
“B”, “X”,
“C”, “X”,
“D”, “X”,
“E”, “Y”,
“F”, “Y”
ETC.
)
Thoughts? Thanks!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 06, 2020 05:45 PM
SWITCH()
functions can’t output “potential” values from a single select field, only the value actually retrieved for a particular record. The formula you have is about as efficient as you can get with a single SWITCH()
. If the issue is the character length of “X, Y, and Z”, you could use those or some other single-character stand-ins and wrap your function in another SWITCH()
like so:
SWITCH(SWITCH({Category}, "A", 1, "B", 1, "C", 1, "D", 1, "E", 2, "F", 2 ...), 1, "Whatever 1 is supposed to mean", 2, "Whatever 2 is actually supposed to mean", ...)
Essentially, the nested SWITCH()
groups your possible values into a manageable set of variables, and the outer SWITCH()
gives you the actual result. This way, you only have to change the value of a group once.