IF formula on Lookup field and Multiple Select

EAugum · ‎Apr 13, 2019

Hi,

I’m stuck on an IF-formula and hoping someone can help me out.

I have two fields;

A Lookup from another table
A Multiple select

IF the Multiple select field is showing the same as the Lookup, I want an IF-formula to output “something”.
E.g.:
Lookup: Done, Done, Working, Working
Multiple select: Done, Done, Working, Working
IF-formula output: “Identlical”
OR
Lookup: Working, Working, Working, Working
Multiple select: Done, Done, Done, Working
IF-formula output: “Not identlical”

Very simple IF-formula: IF(Lookup=Multiple select,“Identical”,“Not identical”)

My problem is that the IF formula always shows the two fields as “Not identical”, no matter what’s the input in the Multiple select.
I have a theory that although the two fields looks to show the same, Airtable reads them differently (commas, spaces etc). With this in mind I have also tried to “translate” the two fields with a concatenate formula, but with no luck.

Any ideas out there?

JonathanBowen · ‎Apr 13, 2019

Hi @EAugum - I’m not totally sure how your data is represented, but I’m thinking one of two ways. The first works out of the box:

In the case, on the multi-select the done and working values are the selectable options.

But I think this isn’t your case given the data you describe - maybe more like this?

Where there are different combinations of done and working in each select option?

If so, then you need to turn the multi-select into a string before doing the comparison:

The Stringify field is:

Multiselect & ''

The Check the Same field is:

IF(Lookup = SUBSTITUTE({Stringify Multiselect}, '"', ''), 'Identical', 'Not identical')

Note that the SUBSTITUTE element removes the double quotes in the stringify field

And you can combine this into a single formula - the final field - with:

IF(Lookup = SUBSTITUTE((Multiselect & ''), '"', ''), 'Identical', 'Not identical')

JB

EAugum · ‎Apr 13, 2019

Hi Jonathan,

Thanks for a very in-depth answer!

Actually, your first example is somewhat close to what my case looks like. However, while I was making this example base, I realized maybe my challenge is that I have more of the same “state” selected in the Multiple select.

I made a simple example base: https://airtable.com/shr1SdLKZfwfYlETD

At the first row in the Employees-table, you will see my issue.
Project State (Lookup) does seem to be matching EmployeeSpecificProjectState (Multiple select), but the IF-formula field says it’s not.

Does this help explain my problem?

Jeremy_Oglesby · ‎Apr 13, 2019

Hi @EAugum,

It may be that one or both of the fields are being treated as an array rather than a string of comma separated values. If they are not coming out as the same type, these two things will never evaluate to being equal even if they look identical.

In the comparison formula, where you reference each field, make sure you append &’’ (ampersand and two single quotes) to each of the field name references to ensure that both are being converted to strings (but don’t copy/paste the ones I typed there - those style of single quotes won’t work in Airtable’s formula editor).

You may want to try doing each separately in its own field to see exactly what its output looks like before combining them into a single formula.

JonathanBowen · ‎Apr 13, 2019

Yep, that’s it - if you stringify the two fields using this formula:

'' & {Your field name}

You can see that two values are different so when you compare you get “not identical”:

So…you probably want to do a SUBSTITUTE on the Emp Spec String to get rid of “comma, space”, then do the comparison.

JB

EAugum · ‎Apr 13, 2019

Thanks both, got it working!

Not really a problem, but due to my incompetence on formulas, I think I have created more new fields than I really need. Any way to combine the two fields doing the Substitute-formula for the EmployeeSpecificProjectState-field?
So, make one formula to remove both the comma and space.

Check my base (updated now) to see what I mean.

Edit: Never mind my question about nested Substitutes. Found some help on this page and solved the problem.

7 fantastic time-saving Airtable substitution formulas

Use these powerful formulas to take control of your data and open up new possibilities in your workflows.