What is the best way to bring in data from another sheet without a direct matching column on both sheets?

Question

My goal is the bring in contact information (Name and email address, 2 separate columns) from a reference sheet {Sheet A} to my target sheet {Sheet B}. I've used {Sheet B} as a reference on many other sheets for the same purpose, however, the data on this particular target sheet is inconsistent with the reference sheet.

For example, my typical use case is:  {Sheet A}: my matching column would be [Association Name] and the data is displayed as a legal entity name (ex. "For Example Homeowners Association")  and I would match that to [Association Name] on {Sheet B} to bring in the [Property Manager] and [Property Manager Email] from {Sheet A}.

The issue I am facing is some of the data on {Sheet B} in the [Association Name] has some data listed as legal entity names and some as a short code name (ex. "For Example HOA") instead of the legal entity naming convention ("For Example Homeowners Association"). So, this will throw a "#NO MATCH" in the columns as there is no matching entry on {Sheet A}.

I considered using a helper column to assign a property ID on both sheets, but again, it's the matching of names to the property ID that I will run into the same issue.

Does anyone have a suggestion on how I can achieve my end goal without having to manually edit over 800 rows of data?

Also, I should clarify that on {Sheet B}, not all entries are using short code. Some are using the legal entity name, so there will be a couple matches, but it will not make a reliable directory with missing contact information.

Melissa Yamada · Answer

Smartsheet’s cross-sheet formulas need an exact match, so even small variations like “HOA” vs “Homeowners Association” return a "#NO MATCH". You need to introduce a consistent key that both sheets can agree on.

The most practical approach is to add a helper column on Sheet B that normalizes the name before the lookup happens. Instead of matching directly on the raw [Association Name], you create a “clean” or “standardized” version of it and use that for your INDEX/MATCH.

For example, you can create a helper column that converts common variations into the same format. A simple version might look like replacing “HOA” with “Homeowners Association” so both formats align. If your inconsistencies are predictable (like HOA, Assoc, etc.), this should fix the majority of mismatches.

Using a property ID can still work, but only if you solve the same problem first—how to reliably assign that ID to each variation. Once everything maps to a consistent key (whether it’s a standardized name or an ID), your existing INDEX/MATCH setup will behave the way you expect.