IF statement to compare string values from two columns and output a into a third based on condition?

Options

Hi all,

I'm trying to build statement and am completely lost. I have 2 columns - one called Current Status and another called Project Status. I'm trying to compare them and output to a third (let's call this "Initiative status" for now).


Under Current Status, this is a drop down cell and about 7-8 options, they include some of the following:

"Submitted"

"Intake In Progress"

"Assessment Completed"

"Technical Assessment Completed"

"Backlog"


The Project Status column is populated if a project and it's status exists using a reference; if there's nothing it will return the value of "Project Not Provisioned/No Status Found". If there is an available status, one of these values will be populated:

Project Status

Project (In-Progress)

Project (On Hold)

Project (Complete)

Project (Cancelled)


Essentially I am trying to build an IF/OR statement or use index and index math, that if one of the Project Status values (excluding the "Project Not Provisioned/No Status Found") are found, that this value is referenced in the third column Initiative Status, else reference the value from the Current Status in this Initiative Status column.

I've tried searching for similar cases such as Multiple IF/OR Formula — Smartsheet Community, but can't really find something as it's not an integer value. I'm not sure how to approach this?


I'm also attaching screenshot of the 3 columns

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this:

    =IF([Project Status]@row <> "Project Not Provisioned/No Status Found", [Project Status]@row, [Current Status]@row)

    In English, if project status is anything other than "Project Not Provisioned/No Status Found", put that project status value here, otherwise, put the Current Status value here.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!