CONTAINS formula
Trying to get a formula to lookup a reference sheet to see if the ID contains the word BASE in it and return with a value SKUBASE if so and SKU if not. I feel like I'm close but just missing something.
I had a formula where if I checked the box "add base" it would simply add the suffix BASE to the id however this is all manual. The reference sheet (second screenshot) is a direct export from my source and I want to make sure I don't miss any values that might contain BASE.
I wrote the formula: =IF(CONTAINS("BASE", {3dcart import Range 1}), SKU@row + "BASE", SKU@row)
but this just seems to add the suffix BASE to every row because its not looking at the id in the row, it's just looking to see if BASE is anywhere in the column and adding it to every row SKU. Seems like I need a VLOOKUP but I know those only return exact values so the BASE suffix would eliminate it.
Best Answer
-
NateP ✭
Thanks James and Kelly. Before I got James' suggestion, I had basically done what he had suggested. I created a couple helper columns in the reference sheet, one which stripped BASE out of the id and one which identified if that row had BASE included.
The formula I used to strip the word BASE out was =IF(BASE@row = 1, LEFT(id@row, (FIND("BASE", id@row) - 1)), id@row)
I think I had a formula in the BASE column which resulted in either just a 1 or 0 but once I got that value, I might have just converted them to basic 1s and 0s instead of a formula. I can't remember now.
Then in the main sheet, I used the following formula: =IFERROR(IF(VLOOKUP(SKU@row, {external reference}, 2, false) > 0, SKU@row + "BASE", SKU@row), SKU@row)
This got me my end goal of looking at the SKU row in the main sheet, and if in the reference sheet that SKU has a BASE suffix, adding that suffix to the SKU in the id row of the main sheet.
Kelly, I tried the INDEX formula you suggested and it didn't seem to give me the results I was looking for. I haven't really used INDEX formulas before so I didn't know how to tweak it to achieve what I needed. Regardless, a little more time on the community pages and I saw some almost completely unrelated post that gave me the idea for the helper column that stripped the suffix out. Thanks for your help. I'll try playing with the INDEX and COLLECT formulas to see if I can get those results.
Answers
-
NateP ✭
In case its not clear, i don't want to use the checkbox anymore
-
Kelly Moore ✭✭✭✭✭✭
Hey@NateP
I believe you are asking to use the SKU in your top screenshot to find the ID in your bottom (Reference sheet) screenshot. This formula will be located in the top screenshot ID column
=INDEX(COLLECT({Reference Sheet ID}, {Reference Sheet ID}, CONTAINS(SKU@row, @cell)), 1)
Remember when using cross sheet references you must physically create each reference - you cannot simply copy paste the formula in your sheet.
Does this work for you?
Kelly
-
NateP ✭
Not quite. What I'm looking for is to see if the SKU in the main sheet, is referenced in the the reference sheet with the suffix BASE and if so, add that suffix in the id column of main sheet. Here's some more examples. You can see that not all SKUs can find a reference with the BASE suffix so they don't get the suffix in the id column, but those that do find it, get the suffix. So searching for BASE and including it is definitely needed in the formula.
-
James Keuning ✭✭✭✭✭There is a more efficient way to do this, but these steps will get you there.
创建一个helper生态的主要表中的列ps the BASE out of the id field. Then in table 2 use match to look for the id in the main table, when match gives you the row, use index to pull the value, then you compare those. You can even use index to pull the word BASE over and concatenate that to your ID.
If you post your table names, field names, and ten rows (text, not screen shots) from each table I can explain it better.
-
Kelly Moore ✭✭✭✭✭✭
Hey@NateP
As you try the different approaches, did you try my method? I believe you will find it does what you describe.
-
NateP ✭
Thanks James and Kelly. Before I got James' suggestion, I had basically done what he had suggested. I created a couple helper columns in the reference sheet, one which stripped BASE out of the id and one which identified if that row had BASE included.
The formula I used to strip the word BASE out was =IF(BASE@row = 1, LEFT(id@row, (FIND("BASE", id@row) - 1)), id@row)
I think I had a formula in the BASE column which resulted in either just a 1 or 0 but once I got that value, I might have just converted them to basic 1s and 0s instead of a formula. I can't remember now.
Then in the main sheet, I used the following formula: =IFERROR(IF(VLOOKUP(SKU@row, {external reference}, 2, false) > 0, SKU@row + "BASE", SKU@row), SKU@row)
This got me my end goal of looking at the SKU row in the main sheet, and if in the reference sheet that SKU has a BASE suffix, adding that suffix to the SKU in the id row of the main sheet.
Kelly, I tried the INDEX formula you suggested and it didn't seem to give me the results I was looking for. I haven't really used INDEX formulas before so I didn't know how to tweak it to achieve what I needed. Regardless, a little more time on the community pages and I saw some almost completely unrelated post that gave me the idea for the helper column that stripped the suffix out. Thanks for your help. I'll try playing with the INDEX and COLLECT formulas to see if I can get those results.
-
Kelly Moore ✭✭✭✭✭✭
Glad you got it working. The intent with the index/collect is a lookup. You shouldn’t have to recreate an answer that is already existing in your reference table. You should be able to bring the answer directly over.
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"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-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+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-08-26T17:06: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-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
