SWITCH() and Multiple Select Fields

[Matt_Caruso]

Can a SWITCH() formula be used to output different criteria for different options and/or combinations of a multiple select field?

Essentially, I want to say: “If multiple select field contains Option 1, return Result A. If it also contains Option 2, return Result A, Result B.”

So far, when I’ve made a SWITCH() function, it will only return if a multiple select field is one thing or another but won’t return if it’s both things.

How could this be built without a zillion characters of different scenarios written out in the formula?

[JonathanBowen]

Hi @Matt_Caruso - as you say a SWITCH() formula is going to get messy pretty quickly. Even with two options:

And this formula:

SWITCH(
{Multi-select}, 
'Option 1', 'Result 1', 
'Option 2', 'Result 2', 
'Option 1, Option 2', 'Result 1, Result 2'
)

I’m still not there as I need to code for the “Option 2, Option 1” choice. Get this to 3, 4, 5 options and you’ve got a big formula on your hands. An easier option is to make the multi-select a link from a reference table:

Now, instead of the multi-select, you link to your option, then do a lookup to get the result:

This will be much easier to handle with multiple options and combinations of options selected.

JB

[AlliAlosa]

Not sure if this would work, but could you make use of the SUBSTITUTE() function? If, for example, “Option 1” always equals “Result A”, and so on, you could write something like…

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Options}, "Option", "Result"), "1", "A"), "2", "B"), "3", "C"), "4", "D")

Of course, this would end up being a long formula as well depending on how many numbers/letters you need to convert. And depending on your use case, this might not work at all. Just thought I’d throw it out there!

[Justin_Barrett]

Both solutions provided are excellent IMO. My gut says that for small scenarios, the SUBSTITUTE option might be preferable, whereas I’d lean toward the link choice for larger setups.

[Matt_Caruso]

Thanks so much for the input. I think I’ve accepted this is too complex to warrant the cost/benefit.

What about comparing two multiple select fields and then creating a third field that just says if they match? The problem I’m running into is that Airtable seems to care what the order of the multiple select options are:

[Justin_Barrett]

When working with the data from multiple-select fields (and several others like rollups, links, etc.), you’re working with arrays. While Airtable does have some array functions, it doesn’t have the ability to compare arrays at this time, nor does it have any way of iterating through an array and performing operations/comparisons on each item (to work around the lack of a direct array-comparison function).

Matt_Caruso:

I think I’ve accepted this is too complex to warrant the cost/benefit.

The linking method proposed by @JonathanBowen isn’t very different from setting up and using a multiple-select field. Not sure what part of it is too complex.