Vlookup match for exact AND approximate matches - single column copying workaround

Julie Hinton

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!

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 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.

Julie Hinton

@Mike Wilday that 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!

Julie Hinton

@Mike Wilday your 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!