Need help with IF statement including text array

[Anne_Sophie_Ass]

Hi all!
I have two fields, both filled with features. I want to see which features are repeated in both fields.

For example, I want a column that would tell me that ‘Feature2’ is in both columns of row 1 (record1), Feature3 and Feature4 are both in the columns of record 2 and 'Feature1 and Feature3 are also both there in record 3.

Maybe a formula like: If the text in column A is similar to the text in column B, then insert it in column C?

I hope this is clear! Thanks in advance for your help!

[Jeremy_Oglesby]

A couple questions for you, @Anne_Sophie_Asselin:

1. How many features are there?
2. Do the features change often? (new additions, removals, name changes)
3. Are you on a Pro plan? (permanent access to blocks, and specifically the Scripting block)

[Anne_Sophie_Ass]

Hi @Jeremy_Oglesby

1. There are 12 features.
2. The features won’t change.
3. I am on the free trial right now which includes all the Pro features. I still have 8 days left so might as well take advantages of it! (but since it’s temporary, a formula might be better than the use of blocks?..)

tx

[Jeremy_Oglesby · Accepted solution]

OK – well, to do this with a formula, you could make an explicit comparison for each of the 12 features. Something like this:

IF(
  AND(
    FIND("Feature1", {expected features}&""),
    FIND("Feature1", {Available Features}&"")
  ),
  "-- Feature1 "
) &
IF(
  AND(
    FIND("Feature2", {expected features}&""),
    FIND("Feature2", {Available Features}&"")
  ),
  "-- Feature2 "
) &
IF(
  AND(
    FIND("Feature3", {expected features}&""),
    FIND("Feature3", {Available Features}&"")
  ),
  "-- Feature3 "
) &
IF(
  ...
)

Just continue repeating that pattern.

[ScottWorld]

This is one of those times when we really need more robust Array functionality built into Airtable! (The array functions are extremely limited at this point.) @Jeremy_Oglesby, do you have any insights on whether or not more enhanced Array functionality is on the horizon?

But luckily for @Anne_Sophie_Asselin, with such a small & non-changing list of features, you could just create a lengthy formula to deal with this. Looks like Jeremy created a great one for you! :slightly_smiling_face:

[Jeremy_Oglesby]

No, I’ve seen/heard nothing about new formulas. I wonder if Scripting block was meant to be their “catch-all” solution for that kind of stuff – which would mean that, eventually, advanced array stuff will only be available to Pro workspaces.

[Anne_Sophie_Ass]

OH YESS!! Thanks @Jeremy_Oglesby it worked perfectly!
Just to make sure I understand well, Could you please explain why you added the &"" at the end of the lines starting with FIND?

Thank you so much for your help!

[Jeremy_Oglesby]

Sure –

The data for the Linked Record fields ({expected features} and {Available Features}) are held as arrays behind the scenes. When you pull the value of a Linked Record field into a formula, it comes in as an array of values.

The FIND() function only works on strings. You ask it to find a sub-string inside a larger string. It won’t work if you ask it to find a sub-string inside an array.

So, to force the Linked Record array into being a string, I concatenate the Linked Record field with an empty string – &"". This coerces the array into behaving like a string, so that the FIND() function works properly.

[ScottWorld]

Jeremy_Oglesby:

No, I’ve seen/heard nothing about new formulas. I wonder if Scripting block was meant to be their “catch-all” solution for that kind of stuff – which would mean that, eventually, advanced array stuff will only be available to Pro workspaces.

Ugh, that would be so terrible for the Airtable platform if that was the direction they were planning on going in. It would really hurt people like myself who aren’t JavaScript experts, and it would also mean that they’ve stopped investing in their own low-code/no-code backend.

[Jeremy_Oglesby]

Ya – here’s hoping they continue to flesh out plain old formula fields still!!