SWITCH() and Multiple Select Fields

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

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!

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.

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:

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).

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.