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”.
Lookup: Done, Done, Working, Working
Multiple select: Done, Done, Working, Working
IF-formula output: “Identlical”
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.
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.
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.
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.