Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Formula novice IF formulas that choose between two columns

Topic Labels: Formulas
Solved
Jump to Solution
241 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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}
)

Any help you could provide would be much appreciated!

1 Solution

Accepted Solutions
Betsy_DeWitt
5 - Automation Enthusiast
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)
Screen Shot 2021-08-04 at 1.48.19 PM

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

See Solution in Thread

2 Replies 2

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}))

Betsy_DeWitt
5 - Automation Enthusiast
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)
Screen Shot 2021-08-04 at 1.48.19 PM

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