Finding Common Multiple Select Options Across Two Fields

Topic Labels: Formulas
762 1
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello All!

I am using a base to pair students based on shared interests they selected in a multiple select field. I am then trying to tell them what interests they had in common. I am easily able to do this when they selected all of the same options using
IF({Are you interested in being paired based on any of the following interests?}={Are you interested in being paired based on any of the following interests?(from People)},{Are you interested in being paired based on any of the following interests?})

The problem I then run into is finding the interests they do have in common when there are additional topics they chose beyond the common ones. Here’s a picture of my fields to better help explain what I’m talking about.
Screen Shot 2021-06-22 at 10.22.48 AM

Thanks for any help or thoughts!

1 Reply 1
10 - Mercury
10 - Mercury

TLDR use the scripting block instead of formulas

Why this is happening

So as you might expect, the = evaluation compares two things and determines if they’re equal. It doesn’t do much more than that. I’m not sure if Airtable is evaluating them as strings or arrays, but just for sake of example, if we had:

"International Student, No Thank you..."
"No Thank you..."

Airtable says, "these things are different - they are not equal. That first one has the phrase “International Student” in it.’

Where you can find a solution

So, whether you’re in Airtable or some other platform, the proper solution here is to iterate through the list of phrases before performing the = comparison. You’d also log any matches you find along the way before returning the list them.

In Airtable, none of that can be done (easily) with formulas. Formulas are meant as short phrases to describe some simple automation, whereas JavaScript can be more expressive. The downside is that JavaScript runs at the push of a button in Airtable, so it wouldn’t be run constantly and automatically like formulas are.