vlookup - Best match?

R

Rory Carroll

I have 2 excel sheets. The company name field in both differ in most cases.
currently I do a vlookup like this
=VLOOKUP(AH5033,companydetails_company.xls!M$2:M$1491,1,FALSE)

For example on the first excel sheet I will have: "Greenberg Ltd" and then
on the other sheet I might have "Greenberg Ltd." or "UK Greenberg"

Is there something called a best match? Its a once off thing I need to do
so efficency is not an issue.

I'd really appreciate the help with this one.
 
T

Tom Ogilvy

there is no artificial intelligence built into the function, but you can
possibly use something along the lines of

=Vlookup("*Greenberg*",range,2,False)

You would have to sort out how to identify the critical string to look for.
 
M

Michael

Could you explain a little more about what you're trying to achieve? Is it
just to see if a company name is in both sheets? Usually, the VLOOKUP
function is used to pull data from a table based on an ID of some kind, such
as monthly sales based on company name or branch.
 
R

Rory Carroll

Ok, I have 2 excel sheets full of data and I want to pull out common rows
where the company name is the same.
Using vlookup gives me only a few of the companies, but I know there are
more that aren't exactly the same name (ie have an extra space or have ltd at
the end, etc)

Using =vlookup(*greenberg*,smallertable,1,false) will not work... I will be
searching by cells eg
=VLOOKUP(AH2,'CompanyName_smallertable.xls'!M$2:M$1491,1,FALSE)

I can't put *'s around AH2, or do I have to do something else with it?
 
Top