0

I am trying to compare two different sheets Sheet one has company names sheet two has company names in two different columns note the company names are not exactly the same, i.e one may have a comma or dot and the other won't

I am trying to find the match from sheet one on sheet two and have it return a value from column 3 (sheet two) I need it to look in column a and if no match look in column b

Unfortunately when i ask for an exact match it returns an error, but otherwise will return a completely different name than the one I want.

Any advice?

Improve this question
1
  • Any lookup that does not have the exact match parameter set requires that the lookup column be sorted. You might want to consider a helper column that strips out commas, periods and double spaces. Post some of the worst examples (redacted of course) and more help may be able to be offered.
    – user385793
    Commented Nov 4, 2014 at 21:46

1 Answer 1

Reset to default
0

I would use the Fuzzy Lookup Add-In for this. It will read in two tables and return the best match (or matches), along with confidence and similarity scores.

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

I cant follow your second paragraph above, but I think for your scenario I would run a first pass using the first pair of columns. Then I would create a new sheet with just the unmatched rows + rows with low confidence and similarity scores. Then I would run a second Fuzzy Lookup using the second pair of columns. The last task would be to collect all the matches together.

If this is a one-off task I would just do those steps manually. If it needs to be repeated I would wrap it in Queries using the Power Query Add-In, to automate the splitting and collation of data. You cant automate calling the Fuzzy Lookup Add-In (yet - I hear they are working on this), but you can automate the other steps.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .