Validation Lookup problems

[JamieDuncan]

I have a sheet in excel named address book, which can be updated within excel(entry to bottom row) via a userform and a macro auto sorts the sheet.
column A is named =ADDRESSBOOK (this contains company names) so that data validation lists can be used on other sheets, the problem is, when there are 2 or more company names the same with different addresses, the validation lists show both entries but LOOKUP can only take one address from the sheet.

Is there a way to have the lookup taking the correct address dependant on which similar company name is selected.

ie. list shows MyCompany LTD 3 times
if i chose the first instance of MyCompany LTD
LOOKUP brings back London
but if i choose the second instance,
LOOKUP brings back Bristol

this is the formula im using at the moment

=LOOKUP(F10,'Address Book'!A1:A64999,'Address Book'!B1:B64999)

F10 being the validation list

 

[mbb4331]

I would combine the CompanyID and address into a new column with a space between the two.

Then use that for your list and lookup.

Column C = trim(Column A) & " " & trim(Column B)