Only use part of a cell value for vlookup

**sa02000** · 10-27-2010, 08:59 AM

I have two lists with different information but the two are connected by company name. The company name is sometimes different in the two lists and most of the time the difference is only "limited" or "LTD" at the end of the company name. Is there a way for me to do vlookup between the two lists but somehow ignore that LTD or Limited in the names?

I tried to do a find LTD and replace with Limited on both lists but that worked for some but for not all as some of the name may not have LTD/Limited at all.

As usual, my lists are huge and to go clean up or check will take a long time and I need to do this once a week or so.

Any help will be appreciated (other way to match the two lists without vlookup).

Jay

**TMS** · 10-27-2010, 09:19 AM

The problem is that you have at least two lists. Each company name could have ltd, ltd., limited, Ltd, Ltd., Limited, LTD, LTD., or nothing at the end. So, even if you can ignore it on the first list, you have a raft of options on the second.

Personally, I'd say bite the bullet and clean up both lists. Then use the second list as a "master" in a Data Validation drop down list on the first sheet. In other words, you can't add anything to the first list unless it already exists in the second. That will force the discipline and ease the lookup task in the future.

It isn't going to get easier ...

Regards

**sa02000** · 10-27-2010, 09:37 AM

TMShucks,
You are exactly right and put the problem even more specifically than I did. I have all these characters which make my vlookup useless. Worse is I don't have any control over both lists and I am receiving it as a standard distribution and I will go back to the separate sources to try to fix it but that might take a while because of impact on others that it might have.

I tried to find all the characters (one by one ofcourse) and replace them with nothing and I seem to get better result on my vlookup but that then completely isolates anything I do going further from the original data that I get (if I needed anything else). I think I will have to just go through thousands of rows and fix.

Thank you,
Jay

**scottylad2** · 10-27-2010, 09:41 AM

You could do a couple of things, either use the wildcard in your lookup or if the company name is always followed by a space then limited or LTD etc, the following would work to isolate the first word and combine with the wildcard

HTH

**scottylad2** · 10-27-2010, 09:44 AM

I've changed the example slightly, to show how more complicated names might look

EDIT was working from within this post, hadn't realised it was showing solved

**TMS** · 10-27-2010, 10:27 AM

You have my sympathy.

My first thoughts are to create a "master" column in each sheet. Copy the company names across, say from column A to column B. This is so that you can retain the originals for reference (in column B). This could be included as part of the following macro(s).

Then use a VBA macro(s) to process each "original" column (A) to remove all the variations (including the previous space). You could have a separate sheet with a list of all the problem words and abbreviations.

So, you retain a copy of the original company names but "normalise" the lists used for the VLOOKUP.

I can't, in all honesty, think of a "nice" way to do this but automated "quick and dirty" seems a better option.

On the basis that you receive this data from elsewhere and possibly pass it on to others, I don't think you can afford to just normalise the data in isolation, as it may prove confusing down the line.

Regards