'Vlookup" for a partial match

[BrotherGomez]

Hi all,

Currently I have 2 columns of names of companies for which I currently use a VLOOKUP to find a yield percentage for said company, and input it in another column. For example in one sheet I would have the name of the company:

Bob's fridges

Then in another sheet I would have in 2 columns the name of the company and the yield percentage:

Bob's fridges 6.7

For this a vlookup would be fine, however the 2 names of the companies do not allways match, as different programmes input the name in slightly different ways. For example, the first sheet may have:

Alans Cookers(4.3%)

(Where the percentage is part of the company name)

The second sheet has

Alans cooker company 4.3% 10.7


(Note the slightly different name -but it is the same company) Where "Bob's cookers company 4.3%" is all contained in one cell as the company name. My question is this: how do I create a "partial" VLookup to return the 10.7 using the top company name. Please note, the first 5 characters of the company name and the percentage could be used as a unique identifier as they are allways unique, for which I have tried extracting the percentage and 1st 5 letters from each to create a unique "identifier" to compare, but with no avail,

Any help would be great,

Many thanks for your time,

Matt

 

[BrotherGomez]

To clarify:

The columns are seperated like this:

"Bobs cooker company 4.3%" *NEW COLUMN* "10.7"

I am looking to extract the 10.7 value

 

[VoG]

You could try a fuzzy lookup http://www.mrexcel.com/forum/showthread.php?p=955137#974873

 

[BrotherGomez]

Hi,

That looks good! I was more looking for the name of it more than anything, didnt know what to search for.

Will try and report back,

Matt