IF statement to compare string values from two columns and output a into a third based on condition?
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
-
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
-
The screenshots didn't come through. Are you able to try providing them again?
-
Hey Paul! See below
-
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!
-
Thanks again Paul - looks like that worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!