IF CONTAINS Formula to return different text values in second column
Hi! I am looking for some help in creating a formula where if a certain word is found in one column, another word is returned in a second column. For example if "Head", "Nasal", or "Spine" is contained in the Procedure column, "Neuro" is returned in the Clinical Specialty column.
I've tried different variations of this and none were working properly.
=IF([email protected], CONTAINS(" NASAL"), "Neuro")
Ultimately, I'd like to have a 5 or 6 different words in the Clinical Specialty column.
If "Head", "Nasal", or "Spine" in the Procedure column return "Neuro" in Clinical Specialty.
If "Chest" or "Cardiac" in Procedure column return "Cardiothoracic" in Clinical Specialty.
If "Extremity", "Forearm", "Ankle", "Foot" in Procedure column return "MSK" in Clinical Specialty.
Thank you!
Best Answer
-
Carson Penticuff ✭✭✭✭✭See if this works for you.
=IF(OR(CONTAINS("Head", [PROCEDURE]@row), CONTAINS("Nasal", [PROCEDURE]@row), CONTAINS("Neuro", [PROCEDURE]@row)), "Neuro", IF(OR(CONTAINS("Chest", [PROCEDURE]@row), CONTAINS("Cardiac", [PROCEDURE]@row)), "Cardiothoracic", IF(OR(CONTAINS("Extremity", [PROCEDURE]@row), CONTAINS("Forearm", [PROCEDURE]@row), CONTAINS("Ankle", [PROCEDURE]@row), CONTAINS("Foot", [PROCEDURE]@row)), "MSK")))
Answers
-
Carson Penticuff ✭✭✭✭✭
See if this works for you.
=IF(OR(CONTAINS("Head", [PROCEDURE]@row), CONTAINS("Nasal", [PROCEDURE]@row), CONTAINS("Neuro", [PROCEDURE]@row)), "Neuro", IF(OR(CONTAINS("Chest", [PROCEDURE]@row), CONTAINS("Cardiac", [PROCEDURE]@row)), "Cardiothoracic", IF(OR(CONTAINS("Extremity", [PROCEDURE]@row), CONTAINS("Forearm", [PROCEDURE]@row), CONTAINS("Ankle", [PROCEDURE]@row), CONTAINS("Foot", [PROCEDURE]@row)), "MSK")))
-
Rita ✭✭This worked, thanks so much!
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/106016/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":49,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
