Search a substring within a text string

[gcefaloni]

Hi,

I have a database of different company names and their respective stock symbols (spreadsheet 1) and in another spreadsheet I have company name and descriptions (spreadsheet 2) that aren't exactly written the same way as they are in my initial database. I would like to try to use some parts of the company name (spreadsheet 2) to those in spreadsheet 1 even if they are not exactly the same. So I was thinking of using the search function but my attempts don't seem to work very well in an array so far. Once I have that matched, I would probably use an index match to refer to another column in my database (spreadsheet 1) to give me the ticker of that company.

I was hoping you'd help me figure out the best way of doing that.

Here is an example of descriptions and my corresponding database.

Spreadsheet 2 descriptions
[TABLE="width: 267"]
<colgroup><col></colgroup><tbody>[TR]
[TD]407 International Inc.[/TD]
[/TR]
[TR]
[TD]407 International Inc.[/TD]
[/TR]
[TR]
[TD]Access Justice Durham[/TD]
[/TR]
[TR]
[TD]AerCap Ireland Capital Designated Activity Co.[/TD]
[/TR]
[TR]
[TD]AerCap Ireland Capital Designated Activity Co.[/TD]
[/TR]
[TR]
[TD]Aeroports de Montreal[/TD]
[/TR]
[TR]
[TD]AIMCo Realty Investors LP[/TD]
[/TR]
[TR]
[TD]AIMCo Realty Investors LP[/TD]
[/TR]
[TR]
[TD]Algonquin Power & Utilities Corp.[/TD]
[/TR]
[TR]
[TD]Algonquin Power & Utilities Corp.[/TD]
[/TR]
[TR]
[TD]AltaLink LP[/TD]
[/TR]
[TR]
[TD]AltaLink LP[/TD]
[/TR]
[TR]
[TD]AltaLink LP[/TD]
[/TR]
[TR]
[TD]Anheuser-Busch InBev Finance Inc.[/TD]
[/TR]
[TR]
[TD]Anheuser-Busch InBev Worldwide Inc.[/TD]
[/TR]
[TR]
[TD]Aroundtown SA[/TD]
[/TR]
[TR]
[TD]Artis Real Estate Investment Trust[/TD]
[/TR]
[TR]
[TD]AT&T Inc.[/TD]
[/TR]
[TR]
[TD]AT&T Inc.[/TD]
[/TR]
[TR]
[TD]AT&T Inc.[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[/TR]
[TR]
[TD]Bank of Montreal[/TD]
[/TR]
[TR]
[TD]Bank of Montreal[/TD]
[/TR]
[TR]
[TD]Bank of Montreal[/TD]
[/TR]
[TR]
[TD]Bank of Montreal[/TD]
[/TR]
[TR]
[TD]Bank of Montreal[/TD]
[/TR]
[TR]
[TD]Bank of Montreal[/TD]
[/TR]
[TR]
[TD]Bank of Montreal[/TD]
[/TR]
[TR]
[TD]The Bank of Nova Scotia[/TD]
[/TR]
[TR]
[TD]The Bank of Nova Scotia[/TD]
[/TR]
[TR]
[TD]The Bank of Nova Scotia[/TD]
[/TR]
[TR]
[TD]The Bank of Nova Scotia[/TD]
[/TR]
[TR]
[TD]The Bank of Nova Scotia[/TD]
[/TR]
[TR]
[TD]BC Telecom Inc.[/TD]
[/TR]
[TR]
[TD]bcIMC Realty Corp.[/TD]
[/TR]
[TR]
[TD]Bell Canada[/TD]
[/TR]
[TR]
[TD]Bell Canada[/TD]
[/TR]
[TR]
[TD]Bell Canada[/TD]
[/TR]
[TR]
[TD]Bell Canada[/TD]
[/TR]
[TR]
[TD]Bell Canada[/TD]
[/TR]
[TR]
[TD]Bell Canada[/TD]
[/TR]
[TR]
[TD]Borealis Infrastructure Trust[/TD]
[/TR]
[TR]
[TD]BP Capital Markets PLC[/TD]
[/TR]
[TR]
[TD]Brookfield Asset Management Inc.[/TD]
[/TR]
[TR]
[TD]Brookfield Infrastructure Finance ULC[/TD]
[/TR]
[TR]
[TD]Brookfield Renewable Energy Partners ULC[/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet 1 database
[TABLE="width: 175"]
<colgroup><col></colgroup><tbody>[TR]
[TD]407 INTERNATIONAL INC[/TD]
[/TR]
[TR]
[TD]407 INTERNATIONAL INC[/TD]
[/TR]
[TR]
[TD]407 INTERNATIONAL INC
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 175"]
<colgroup><col></colgroup><tbody>[TR]
[TD]AEROPORTS DE MONTREAL[/TD]
[/TR]
[TR]
[TD]AEROPORTS DE MONTREAL

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 175"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ALGONQUIN POWER CO[/TD]
[/TR]
[TR]
[TD]ALGONQUIN POWER CO

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 175"]
<tbody>[TR]
[TD="width: 175"]ANHEUSER-BUSCH INBEV FIN[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 175"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ALTALINK INVESTMENTS LP[/TD]
[/TR]
[TR]
[TD]ALTALINK INVESTMENTS LP[/TD]
[/TR]
[TR]
[TD]ALTALINK LP[/TD]
[/TR]
[TR]
[TD]ALTALINK LP[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 175"]
<colgroup><col></colgroup><tbody>[TR]
[TD]BANK OF MONTREAL[/TD]
[/TR]
[TR]
[TD]BANK OF MONTREAL
[TABLE="width: 175"]
<tbody>[TR]
[TD="width: 175"]BANK OF NOVA SCOTIA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 175"]
<tbody>[TR]
[TD="width: 175"]BCIMC REALTY CORP
[TABLE="width: 175"]
<tbody>[TR]
[TD="width: 175"]BROOKFIELD ASSET MAN INC
[TABLE="width: 175"]
<tbody>[TR]
[TD="width: 175"]BROOKFIELD RENEWABLE PAR
[TABLE="width: 175"]
<tbody>[TR]
[TD="width: 175"]BROOKFIELD INFRA FIN ULC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 175"]
<tbody>[TR]
[TD="width: 175"]BOREALIS INFRASTR TRUST[/TD]
[/TR]
</tbody>[/TABLE]



Thanks a lot for your help in advance!!!

 

[=ODIN=]

Sorry I might be misunderstanding here. But I think what would help you is Microsoft's fuzzy lookup addin.

https://www.microsoft.com/en-us/download/details.aspx?id=15011