Compare similar company names between two tables and return one of the values

[realtoast]

I have two tables, each with around 100 unique companies. One table has a field, Client, with rows containing business names. The other table has a field, Organization Name, with rows containing the same businesses, but with the company names slightly different.

Table 1 is revenue per company, Table 2 is summarized expenses per company. I need to compare revenue to expenses, per company.

Table 1 shows monthly company revenues and Table 2 is a list of company expenses. There is no index or common unique identifier in either table. The only way I can join these two tables is by names, but the names are slightly different. In most instances, the difference is Table 1 has the full legal name of the company, and Table 2 has a more informal version. Plus, Table 2 has dozens of records per company. For instance, ABC Plumbing, Inc. and ABC Plumbing.

Is there a way, in Table 2, to compare the two tables and return the company name value in Table 1 to a field in Table 2, say, called "Normalized"? The purpose is to create joinable fields.

Thanks!

Table 1
CompanyName	City	State
ABC Plumbing, Inc.	Charlotte	NC
Charcoal Is Us, LLC	Sacramento	CA
Doorknob Emporium, Inc.	Phoenix	AZ
Very Small Rocks, LLC	Chicago	Il
Cous Cous Cous, LLC	Boise	ID

Table 2
CompanyName	Normalized	City	State
ABC	=formula here	Charlotte	NC
Charcoal Is Us		Sacramento	CA
Doorknob Emporium		Phoenix	AZ
Very Small Rocks		Chicago	Il
Cous Cous Cous		Boise	ID

 

[eiloken]

try this:

Normalized=INDEX($A$2:$A$6,MATCH(A14&"*",$A$2:$A$6,0))

 

[realtoast]

try this:

Normalized=INDEX($A$2:$A$6,MATCH(A14&"*",$A$2:$A$6,0))

Outstanding!!! Thank you so much!