Replace Multiple Values with Multiple Values

MLK
MLK ✭✭
inFormulas and Functions

I have a Status column which is a dropdown list. I would like a formula that checks the Status column and returns a different number for each item in the list. Any suggestions on how to do this?

STATUS. RETURNED VALUE

"NA". "1"

"Not Started". "2"

"Priors Pending" "3"

and so forth.....

· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
You would just use<\/p>

=[Column B]@row \/ [Column A]@row <\/p>


<\/p>

EDIT: Then apply the percentage format to the column.<\/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":111167,"type":"question","name":"Looking to Countif a person's name is contained in a column that allows for multiple contacts.","excerpt":"=COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row)) the OTP Goals Project Lead is the sheet that has the column that is a contact list that allows for multiple contacts. If a person's name is contained in that column and it matches the name in the column on the metrics sheet called Contact - then I want it to count…","snippet":"=COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row)) the OTP Goals Project Lead is the sheet that has the column that is a contact list that allows for multiple contacts. If…","categoryID":322,"dateInserted":"2023-10-04T02:50:21+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T13:57:49+00:00","insertUserID":131821,"insertUser":{"userID":131821,"name":"Melitta","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Melitta","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7FjR-2zeSSY!zOlQs_Tjxjo!J3yyx3hqT9S","dateLastActive":"2023-10-04T17:39:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-10-04T17:50:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3392817490,"url":"https:\/\/community.smartsheet.com\/discussion\/111167\/looking-to-countif-a-persons-name-is-contained-in-a-column-that-allows-for-multiple-contacts","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111167\/looking-to-countif-a-persons-name-is-contained-in-a-column-that-allows-for-multiple-contacts","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111167,"commentID":398349,"name":"Re: Looking to Countif a person's name is contained in a column that allows for multiple contacts.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398349#Comment_398349","dateInserted":"2023-10-04T13:57:49+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-10-04T17:50:24+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-10-04T11:48:58+00:00","dateAnswered":"2023-10-04T09:06:57+00:00","acceptedAnswers":[{"commentID":398315,"body":"

Hi @Melitta<\/a> <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row,@cell)<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":111156,"type":"question","name":"Replace Multiple Values with Multiple Values","excerpt":"I have a Status column which is a dropdown list. I would like a formula that checks the Status column and returns a different number for each item in the list. Any suggestions on how to do this? STATUS. RETURNED VALUE \"NA\". \"1\" \"Not Started\". \"2\" \"Priors Pending\" \"3\" and so forth.....","snippet":"I have a Status column which is a dropdown list. I would like a formula that checks the Status column and returns a different number for each item in the list. Any suggestions on…","categoryID":322,"dateInserted":"2023-10-03T22:03:18+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T15:18:10+00:00","insertUserID":151465,"insertUser":{"userID":151465,"name":"MLK","url":"https:\/\/community.smartsheet.com\/profile\/MLK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zbHV85tf1Hw!XJ1Un0NmDCQ!G7y689MNH3u","dateLastActive":"2023-10-04T15:17:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151465,"lastUser":{"userID":151465,"name":"MLK","url":"https:\/\/community.smartsheet.com\/profile\/MLK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zbHV85tf1Hw!XJ1Un0NmDCQ!G7y689MNH3u","dateLastActive":"2023-10-04T15:17:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3392804488,"url":"https:\/\/community.smartsheet.com\/discussion\/111156\/replace-multiple-values-with-multiple-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111156\/replace-multiple-values-with-multiple-values","format":"Rich","lastPost":{"discussionID":111156,"commentID":398378,"name":"Re: Replace Multiple Values with Multiple Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398378#Comment_398378","dateInserted":"2023-10-04T15:18:10+00:00","insertUserID":151465,"insertUser":{"userID":151465,"name":"MLK","url":"https:\/\/community.smartsheet.com\/profile\/MLK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zbHV85tf1Hw!XJ1Un0NmDCQ!G7y689MNH3u","dateLastActive":"2023-10-04T15:17:25+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-10-04T15:17:23+00:00","dateAnswered":"2023-10-04T05:59:20+00:00","acceptedAnswers":[{"commentID":398307,"body":"

Hi MLK<\/strong><\/a>,<\/p>

You can try the below formula-<\/p>

=IF(Status@row = \"NA\", 1, IF(Status@row = \"Not Started\", 2, IF(Status@row = \"Priors Pending\", 3, \"//www.santa-greenland.com/community/discussion/111156/\")))<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Thank You!<\/p>

Anjanesh Vaidya<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Dividing to Create a %

    I would like to use the data in Column A and B to create a percentage complete by row NOT by parent/hierarchy. In Excel this would be =sum(Column B/Column A) = %. What is the formula for Smartsheet?

  • Looking to Countif a person's name is contained in a column that allows for multiple contacts.

    =COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row)) the OTP Goals Project Lead is the sheet that has the column that is a contact list that allows for multiple contacts. If a person's name is contained in that column and it matches the name in the column on the metrics sheet called Contact - then I want it to count…

  • Replace Multiple Values with Multiple Values

    I have a Status column which is a dropdown list. I would like a formula that checks the Status column and returns a different number for each item in the list. Any suggestions on how to do this? STATUS. RETURNED VALUE "NA". "1" "Not Started". "2" "Priors Pending" "3" and so forth.....