IF AND where an argument contains a string within comma separated values

[Airtable_Ops]

Hi, I have a table with 3 columns, “Usage”, “Clients” and “Status”. “Usage” is a single select and can only be “Platform” or “Client”. “Clients” is a link record that allows me to select multiple client records from another table. “Status” is a lookup field returning the status of each client from the client field, it displays the results in a comma separated list. Possible values for “Status” are “Live”, “Suspended” and “Terminated”.

I need to write a formula that satisfies the following conditions:

• If “Usage” = “Platform” set the cell as “Active”
• If “Usage” = “Client” AND the value “Live” is present anywhere in “Status” column then set the cell as “Active”
• If “Usage” = “Client” AND the value “Live” is not present anywhere in “Status” column then set the cell as “Inactive”
• If “Usage” = “Client” AND the “Status” column is blank then set the cell as “Inactive”

The formula’s I’ve tried so far don’t work. I’ve tried using IF(AND statements and IF(SEARCH statements but I can’t get them to work. Is someone able to indicate how this formula should be written?

[Claudio · Accepted solution]

You can use something like this:

IF(Usage='Platform','Active',IF(FIND('Live',Status&" ")>0,'Active','Inactive'))

This assumes that “Usage” can’t be blank, which means that if it’s not “Platform”, it must be “Client”.

If “Usage” can be blank and (for that same record) “Status” can be “Live”, and you don’t want the result to be “Active”, then you can add another IF to determine whether “Usage” is blank and, if yes, return something like “Incomplete” or “No Usage”.

For example:
IF(Usage, IF(Usage='Platform','Active', IF(FIND('Live',Status&" ")>0,'Active','Inactive')), 'Incomplete')

BTW, ‘Status&" "’ turns the contents of the lookup field into text that can be searched.

[Airtable_Ops]

@Claudio This works perfectly! You have no idea how long I spent trying to work this out, I can’t believe it was this simple.

Many thanks