Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

SWITCH Result to be Single Select?

Topic Labels: Formulas
909 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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.