Ok, the easiest way to get this done with VLOOKUP() is to have a table with
the city names in the left-most column of a table. The rest of the
information can be in pretty much any order, but we'll assume it's laid out
this way:
A B C D E
1 city ST zip Bus.Name street
..
..
..
20 city ST zip Bus.Name street
This is on a sheet named [ShippingInfo], so the address of the table area is
'ShippingInfo'!$A$1:$E$20
While you could have the dropdown list with city names on several sheets, we
will assume it's just on one, a sheet named [Drop Down Sheet]. And on that
sheet, the drop down is in cell C3.
For any place you need to get part of the address you can put in a formula
like this:
=VLookup('Drop Down Sheet'!$C$3,'ShippingInfo'!$A$1:$E$20,#,False)
where ,#, is a number from 1 to 5 for our table:
,1, will return the city name again
,2, would return the state
,3, would return the zip code
,4, would return the business name, and
,5, would return the street address.
It would help you if you used a Named Range for the table. To do that you'd
go to the sheet it is on and select the entire table from A1 to E20 and then
up in the little area where it normally shows the address of the cell you are
in, type a name for the table (has to be a single word and you MUST terminate
it with the [Enter] key), such as ShippingTable
Then you can substitute ShippingTable in the formulas instead of the sheet
name and cell range of the table. If you need to add new cities later, add
them as new rows WITHIN the existing list (after row 1 and before what is now
row 20) and the formulas will continue to work without you having to go back
and revise them to account for new entries. Formula would look something
like this (again, substitute the appropriate number where I have put in the #
symbol):
=VLookup('Drop Down Sheet'!$C$3,ShippingTable,#,False)
Hope this helps.
