Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

SWITCH Result to be Single Select?

Topic Labels: Formulas
2127 1
cancel
Showing results for 
Search instead for 
Did you mean: 
epiic
4 - Data Explorer
4 - Data Explorer

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!

1 Reply 1

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.