If and Match Formula Help

Melissa Larkin
Melissa Larkin ✭✭
edited 8:42AM inFormulas and Functions

Hello - I have 2 sheets. One that shows all the fields from our on-prem environment and another that shows all the fields that were migrated to our sandbox via a migration tool. I want to know if any fields were not migrated from our on-prem environment to the sandbox.Any suggestions on an equation to use?I tried the following question and it worked for the first few rows but then I got an invalid data type: =IF(MATCH(Helper@row, {Metadata - Sandbox - Test - 2023_08_18 Range 1}), "Migrated to Sandbox"). I'm not sure what I'm doing wrong :(

image.png


· Share on Facebook Share on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi@Melissa Larkin

    What I would do in this instance is actually use a COUNTIFS formula to "count" the number of rows in your second sheet that have the same data as in your current sheet.

    The COUNT will be 0 if it hasn't been migrated, or 1 if it has.

    Try this:

    =IF(COUNTIFS({Metadata - Sandbox - Test - 2023_08_18 Range 1}, Helper@row) > 0, "Migrated to Sandbox", "Not Migrated")

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter

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
See if this works for you:<\/p>

=IF(Year@row = YEAR(TODAY()), \"Current\", IF(Year@row < Year(today()),\"Past\", IF(Year@row > YEAR(today()), \"Upcoming\", 0)))<\/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":109192,"type":"question","name":"Unparseable Message on Column Formula","excerpt":"I can't get this formula to work in my spreadsheet, any suggestions on how to fix it? =countif([Column5]@row11:[Column16]@row11, \"*\"&[Column17]$10&\"*\")","snippet":"I can't get this formula to work in my spreadsheet, any suggestions on how to fix it? =countif([Column5]@row11:[Column16]@row11, \"*\"&[Column17]$10&\"*\")","categoryID":322,"dateInserted":"2023-08-21T13:43:16+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T17:29:30+00:00","insertUserID":165403,"insertUser":{"userID":165403,"name":"ReNae Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/ReNae%20Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T17:24:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-21T18:05:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":31,"score":null,"hot":3385267366,"url":"https:\/\/community.smartsheet.com\/discussion\/109192\/unparseable-message-on-column-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109192\/unparseable-message-on-column-formula","format":"Rich","lastPost":{"discussionID":109192,"commentID":391660,"name":"Re: Unparseable Message on Column Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391660#Comment_391660","dateInserted":"2023-08-21T17:29:30+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-21T18:05:33+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-08-21T13:55:59+00:00","dateAnswered":"2023-08-21T13:44:49+00:00","acceptedAnswers":[{"commentID":391601,"body":"

It looks like you are trying to count how many cells between [Column5] and [Column16] (on each row) contains the value in [Column17]$10. Is that correct?<\/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":109190,"type":"question","name":"How to remove the last letter from a text field.","excerpt":"I have a list of data: 8888 H 88 H 888 M 8.888 M How do I remove the H & M to leave a number that I can use in a calculation?","snippet":"I have a list of data: 8888 H 88 H 888 M 8.888 M How do I remove the H & M to leave a number that I can use in a calculation?","categoryID":322,"dateInserted":"2023-08-21T12:31:17+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T15:57:28+00:00","insertUserID":135590,"insertUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-08-21T13:45:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-21T18:05:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":34,"score":null,"hot":3385257525,"url":"https:\/\/community.smartsheet.com\/discussion\/109190\/how-to-remove-the-last-letter-from-a-text-field","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109190\/how-to-remove-the-last-letter-from-a-text-field","format":"Rich","lastPost":{"discussionID":109190,"commentID":391615,"name":"Re: How to remove the last letter from a text field.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391615#Comment_391615","dateInserted":"2023-08-21T15:57:28+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-21T18:05:33+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-08-21T14:43:19+00:00","dateAnswered":"2023-08-21T13:39:11+00:00","acceptedAnswers":[{"commentID":391595,"body":"

Try wrapping it in a VALUE function.<\/p>

=VALUE(<\/strong>original_formula)<\/strong><\/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

  • Auto populate with current, past, or upcoming text based on year

    I am trying to auto populate a column with "Current", "Past", or "Upcoming" based on the year column. So for a row with 2023, I would want "Current", but would want "Past" or "Upcoming" based on what's in the Year column. I've put in the following formula but get an error (#UNPARSEABLE) =IF(Year@row = YEAR(TODAY()),…

  • Unparseable Message on Column Formula

    I can't get this formula to work in my spreadsheet, any suggestions on how to fix it? =countif([Column5]@row11:[Column16]@row11, "*"&[Column17]$10&"*")

  • How to remove the last letter from a text field.

    I have a list of data: 8888 H 88 H 888 M 8.888 M How do I remove the H & M to leave a number that I can use in a calculation?