help with vlookup or if you have other suggestions

  • Thread starter Thread starter Mel
  • Start date Start date
M

Mel

Hello,

I have two different excel sheets to combine into one.
Both contains the names of our suppliers, however the
names are not perfect:

Worksheet 1 (A1): Paul's Trucking Worksheet 2(A1) Paul's
Trucking, LLC. I thought a lookup table would help to
combine these but since the names are slightly different I
can't get it to work. Is there a better way to accomplish
this or will I have to redo the worksheets by hand?

Thanks,

M.
 
Hi Mel,

If there aren't too many different names, then you could use
Find/Replace...

Select all sheets
Edit > Replace
Find what: ABC*
Replace all > ABC Company Limited (proper name)

An alternative would be to use an array formula such as this:

=VLOOKUP(LEFT(A1,10),LEFT(Sheet1!$A$1:$B$10,10),2,0)
entered using CTRL+SHIFT+ENTER

Here, the formula extracts the first 10 characters (starting from the
left) from the lookup value in A1, and matches it against the first 10
characters (starting from the left) from the first column in the lookup
table.

You'll need to be careful, though, since it's possible that the first 10
characters for two different names may be the same. In this case, the
formula would return the first occurrence.

You may want to change the number of characters to look at, in either
direction (more or less than 10), depending on your data.

Hope this helps!
 
Back
Top