JamieDuncan
Board Regular
- Joined
- Aug 23, 2006
- Messages
- 132
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
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