Formula novice IF formulas that choose between two columns

Topic Labels: Formulas
Solved
882 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I obviously don’t know what I’m talking about, so apologies in advance. I was inspired by this Team Schedule using Airtable blog post where they have a formula column that delivers “team member + Status” so I’m trying to create a formula that achieves something similar, but is a little more complex:

My columns are:
Brand = lookup from another table example “Client 1”
Project = lookup from another table example "Awesome Website"
Person = lookup from another table example “Betsy”
Status = single select example “Working” or “OOO”

My goal is to get the formula to deliver [Shortened {Brand} name | {project}] - Status] or {person} - Status where the result EITHER shows:
shortened brand name + project + status (example C1 | Awesome Website - Working)
OR
person + status (example Betsy - OOO)

I’ve poked around several topics and the formula database trying to figure it out but I don’t know coding syntax or complex formulas. Here’s what I have so far:
Attempt #1 (kind of works?)

``````{Brand} &  " | " & AND({Project}, {Person}) & " - " & {Status}
``````

Attempt #2 (doesn’t work)

``````IF(
{Brand}, SWITCH({Brand},
"Client 1", "C1",
"Client 2", "C2",
"Client 3", "C3") & " | " &
) & IF(
AND {Project},
{Person}), & " - " & {Status}
)
``````

Attempt #3 (doesn’t work)

``````IF(
{Brand}, SWITCH({Brand},
"Client 1", "C1",
"Client 2", "C2",
"Client 3", "C3") & " | " &
) & IF(
OR(
FIND ("OOO", {Project},
{Person}), & " - " & {Status}
)

``````

1 Solution

Accepted Solutions
5 - Automation Enthusiast

I’d love to take full credit for this but I had IRL help to get the formula to work. Nevertheless the solution:

``````IF(
{Brand} = BLANK(),
{Person} & " - " & {Status},
{Brand}  & " - " & {Project} & IF({Status} = BLANK(), "", " - " & {Status})
)
``````

(attaching a picture also of how the setup was explained to me)

It also seems to work as simply as this (assumes brand and project operate as a pair in that they are both full or both empty

``````{Brand} & " " & {Project} & {Person} & " - " & {Status}
``````

thanks for the help @augmented I appreciate the response

2 Replies 2
10 - Mercury

Hi Betsy. I haven’t actually tried my proposed solution below, so it might have unbalanced parens. The idea is simple, if you have {Brand} and {Project}, then construct the preferred string. If not, but you do have {Person}, then construct the secondary string. I didn’t put checks in for {Status} but you can if you want. Good luck.

IF(AND({Brand},{Project}), {Brand} & ’ | ’ & {Project} & ’ - ’ & {Status}, IF({Person}, {Person} & ’ - ’ & {Status}))

5 - Automation Enthusiast

I’d love to take full credit for this but I had IRL help to get the formula to work. Nevertheless the solution:

``````IF(
{Brand} = BLANK(),
{Person} & " - " & {Status},
{Brand}  & " - " & {Project} & IF({Status} = BLANK(), "", " - " & {Status})
)
``````

(attaching a picture also of how the setup was explained to me)

It also seems to work as simply as this (assumes brand and project operate as a pair in that they are both full or both empty

``````{Brand} & " " & {Project} & {Person} & " - " & {Status}
``````

thanks for the help @augmented I appreciate the response