Vlookup on incomplete data

Hi Excel Gurus

I have been cracking my head for a few months on this particular challenge. I scoured the net to find a solution and the closest that I have come is via this forum. However, the solution is not quite yet there. Perhaps my naivety in excel is not allowing me to see a solution. So I turn to the master for help!

The Challenge

I would like to lookup account no’s from a customer master list from an external list. The only way to lookup is using the customer’s name. There are a couple of challenges to this:
1. The customer’s name in the master list and external list might not be the same although they have the same account no. For example name in the external list is Invitrogen Ltd UK and name in the master list is Invitrogen. Both have the same account no.

2. Some customers has the same name or close to it but different ac no’s. Vlookup only gives me the 1st ac no. For example in the external list I have Fujitsu Services L and in the master list I have Fujitsu Services Limited with 3 different ac no’s. I want a hit on all 3 accounts.

I have attached the spreadsheet for better understanding of the challenge. Hope to hear a positive answer from the masters!

P.S. I have trimmed the Cust List significantly as the file size exceeded 1MB.

Cheers

Arasan

Not exactly sure what you're looking for, but it seems the problem is how to allow Excel to find matches. For example, you have (as you indicate) 3 instances of "Fujitsu Services", but you also have another close match -- "Fujitsu S". I presume they are the same. So your solution would be either to scrub your data (which doesn't seem to be an option) or come up with a rule for how you define "close enough".

The attached is an example of how you could approach it using the assumption that anything up to but not including the second space in any name defines close enough. So AMCOR FLEXIBLES UK would be shortened to AMCOR FLEXIBLES for lookup purposes. You do that at both ends and you can at least find matches.

If this general approach doesn't work and you can put together a set of rules that defines what does and doesn't qualify as a match, someone can probably help.

Oh, and to keep the file size down so I could upload the attachment, I had to trim the customer list (sorry, but there will be formula bloat in pretty much any solution).

bentleybob, what do you add or leave out of this formula to pick-up at the second space?

I am trying trunate on the 2nd space:

Leary, Robert (Rob)

to

Leary, Robert

...so I can match the name format in another file that does not include the nickname.

The example truncates at the second space. To truncate at the third space, the code would be (in External List!H2 and copied down):

Please Login or Register  to view this content.

You of course have to duplicate this in Cust List (in Cust List!G3 and copied down):

Please Login or Register  to view this content.

I got this to somehwhat work:

=LEFT($B3,IFERROR(FIND(" (",TRIM($B3),IFERROR(FIND(" (",TRIM($B3)),1)+1),IFERROR(FIND(" (",TRIM($B3)),LEN($B3))))


However, it is truncating the ( but leaving 1 space, so the vlookup is not matching the names that did have nicknames only the ones that did not.

Got yours to work by looking for the "("

Thanks!

Sorry, FSUM. Thought you were the OP. Glad you worked it out yourself, since that's the idea behind the forum!

Thanks BentleyBob,

That certainly helps!.........however I still have many non hits. Is there a reason why? Some accounts are truncated eg. Denvir is Denvi which probably is giving rise to the non hit.

What about the rest? Is there a workaround for this?

Cheers

Arasan

Yes, I noticed that there were misses when I created the formulas for FSUM, so if you still want to truncate at the second space, use the following in Cust List:

Please Login or Register  to view this content.

... and the following in External List:

Please Login or Register  to view this content.

Please let me know if you still have problems.

Hi Bentleybob,

Your tweak worked!.........thank's mate, although its not giving me a 100% hit (80%), I think this is pretty good compared to what I was working on. For the remaining 20% the fault lies with the data itself so I don't think we can do anything about it.

Cheers

Arasan