0

I've been looking everywhere and I can't seem to get the results I want. I haven't used Excel in a while and am having trouble doing partial matches from two columns. I want to find partial matches of column A in column B and return the position where the matches are. Column A contains a data set of company names and column B contains another set. I want to make sure that ABC llc comes back as a partial for abc inc.

Is there a formula for that? I want to find a partial anywhere in the columns and for multiple companies.

Example: 

ColumnA
1st Choice Staffing
84 Lumber Company
Accent Hiring Group
Accent Hiring Group
Accountants Intl.

ColumnB:
1199SEIU Benefit and Pension Funds
171263 Canada Inc
1-800-FLOWERS.COM, Inc.
1St Franklin Financial Corporation
1St Source Bank
pnuts
  • 58,317
  • 11
  • 87
  • 139
Kimberly Wong
  • 1

2 Answers2

1

VLOOKUP is fantastic as long as you are looking for exact matches. The TRUE / FALSE argument doesn't really serve the purpose you'd thought it serves. When you're looking for things that are not so black and white, you'd need some fuzzy logic here.

A quick example of where VLOOKUP falls short :

  • Song name A = "Beethoven : Fidelio op.72"
  • Song name B = "Beethoven : Fidelio opus 72"

Microsoft released a free add in to Excel (compatible with most recent versions) that enables fuzzy vlook up'ing. Get the file (add-in + documentation + example) here :

http://www.microsoft.com/en-us/download/details.aspx?id=15011

And to Pnuts's point, the algorythm is based on Levenshtein distance.

Greg
  • 354
  • 1
  • 5
  • 11
0

Another add-in that may be of interest is the PowerUps add-in for Excel. The PowerUps add-in works in Excel 2003 thru 2013 and it has a function called pwrVLOOKUP that does fuzzy vlookups and operates basically the same as vlookup, just fuzzy if you want. An example is posted on the page here: http://officepowerups.com/2013/10/23/fuzzy-vlookup-in-excel/. It would do just what you're looking in your example. If not a pwrVLOOKUP, then the pwrMATCH would do the trick.

Charlie
  • 31
  • 1