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
Bookmarks