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

summetg

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

Jeff Reisman

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.

Paul Newcome

The screenshots didn't come through. Are you able to try providing them again?

summetg

Hey Paul! See below

Jeff Reisman

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.

summetg

Thanks again Paul - looks like that worked!