Advanced exact partial match VLOOKUP help

[ssigl]

Hey Everyone

I really hope someone can help me with this!

I am doing a VLOOKUP match-up between company names in one column, and company names in another column. The company names have an inconsistent format (like HeroSwap, Inc. in one column and HeroSwap Incorporated in the other). I've cleaned out one column to now only consist of the company name (HeroSwap).

I am currently using this formula:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545}</style>=IF(ISNA(VLOOKUP(K2&"*",U:U,1,FALSE)), " - ",VLOOKUP(K2&"*",U:U,1,FALSE))

What I'm looking to improve is to:

1. Make sure that the clean company name only matches up with a full word in the other cell strings (right now HeroSwap will match with Heroswapper)
2. Sometimes there are several different matches in the column I am using as my table_array. Is there anyway to show all matches in the string, separated by a comma or a similar solution?

Any expert help is greatly (!!!) appreciated.

Thanks very muchly

 

[philaugust2004]

can you post an example of what you have and what desired outcome is? That would make it easier to help

 

[MUKESHY12390]

you don't you create "unique column" by combining two or three column.

eg. you have ComanyName, Region, Material, Owner

in this case ,take left(company,4digit)&region&material and then do lookup.


I'm trying say creating unique ID this way or other way would solve your problem.

 

[ssigl]

Okay, so an example could be this:

Column A has 10 company names:

Sabricota Inc.
Unified Corp.
Eternal Ltd.
Superfire, Inc.
MRWS LLC
Appdo Incorporated
Apricot, Inc.
Uturn S.A.
Riffle S.R.L.
UberCon Inc.

Column B has all the cleaned Company names, without the Inc., Ltd., LLC etc.
I use an individual cell as a lookup_value (Abricot)

When doing this with the formula i wrote in the description it will lookup the value in my chosen column, but it will return the first match it finds (in this case Sabricota, Inc. because it contains the word Abricot) and will leave out the one i really wanted (Abricot, Inc.)

What can i do to solve this problem? And possibly even show all of the matches?

 

[Tetra201]

... I am currently using this formula:

=IF(ISNA(VLOOKUP(K2&"*",U:U,1,FALSE)), " - ",VLOOKUP(K2&"*",U:U,1,FALSE))

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454</style>What I'm looking to improve is to:

1. Make sure that the clean company name only matches up with a full word in the other cell strings (right now HeroSwap will match with Heroswapper)...

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=IFERROR(VLOOKUP(K2&" *",SUBSTITUTE(U2:U1000,","," ")&" ",1,FALSE), " - ")

 

[ssigl]

Unfortunately, this doesn't resolve the issue. It will still match Beli with Belitz & Garthoff. I want to make a condition that means it will only return matches of the lookup_value full company name (full string), and not to just find names that contain the lookup_value (like Beli is the beginning 4 letters in Belitz). I'd rather have this do a negative match.

 

[Tetra201]

Unfortunately, this doesn't resolve the issue. It will still match Beli with Belitz & Garthoff...

It does not in my test sheet -- because the search pattern is "Beli *" with a space between the letters and the asterisk.
Please make sure you have entered my formula exactly as shown in Post #5.

 

[ssigl]

Thanks, it works now. Sorry about that.

If anyone has a solution to number 2, please do tell

Thanks!