Vlookup match for exact AND approximate matches - single column copying workaround
Hello!
I have a process that currently involves multiple sheets and requires manual copy & paste to complete. This is not a scalable solution for our business, so I’m trying to build a workaround to combine data from several sheets and automatically update data in some sheets when certain actions are triggered. Some of these sheets receive data via form submissions, some manually, and some with copy/move automations.Since copying only some columns within a row to another sheet with an action is not possible, I am trying to build formulas that can pull data from other sheets based on some criteria (vlookup).
All of the sheets have one column in common, Company Name. In one of these sheets there is also a unique company ID for each company name. My idea is that I can pull that unique Company ID into other sheets with a vlookup based on the company name. The problem I have is that the name is not always consistent on every sheet (the person that input the name on one sheet may have altered it slightly, added a suffix, etc). To get around this I planned to use TRUE in my formula to get an approximate match. This is not ideal but could be a possible solution. When I do this, I get some matches on approximate, but #NOMATCH when the value is exact. Is there a way to get around this?
Maybe I am going about this the wrong way and there is a better solution for automating this process while maintaining accurate consistent data in Smartsheet. Here is a screenshot of the columns, the highlighted rows are the exact matches returning a #NOMATCH
Any ideas are appreciated!
Best Answer
-
Mike Wilday ✭✭✭✭✭✭What about creating a dropdown list of companies and requiring a user to input one already on the list? You could provide a "not listed" option which could allow for a custom cell to appear on the form to enter the company name. You could then set up an automation to alert you or someone to add that name to the dropdown list for future input? This would help stop the misnaming maybe.
Answers
-
Mike Wilday ✭✭✭✭✭✭
What about creating a dropdown list of companies and requiring a user to input one already on the list? You could provide a "not listed" option which could allow for a custom cell to appear on the form to enter the company name. You could then set up an automation to alert you or someone to add that name to the dropdown list for future input? This would help stop the misnaming maybe.
-
@Mike Wildaythat is actually a good idea to solve one of my issues, although I may not be able to get that through security approval since some of these forms are external facing. But, it's a great idea for me to look into!
-
@Mike Wildayyour suggestion on the source sheet along with Data Mesh seems to be the answer to my problem. Thanks for the suggestion!
-
Mike Wilday ✭✭✭✭✭✭
You're welcome!
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/75938/\")<\/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":38,"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":"
