addresses and If function

[Dominique Feteau]

I have a column in a worksheet called "office" and one called "address". a
list of each office address is in another sheet like so:

Office Location
New York Address


How can i setup a function to where if New York is typed into a cell in the
office column that the address cell in that row will be automatically
populated with the appropriate address.

 

[Paul]

I have a column in a worksheet called "office" and one called "address". a
list of each office address is in another sheet like so:

Office Location
New York Address

How can i setup a function to where if New York is typed into a cell in the
office column that the address cell in that row will be automatically
populated with the appropriate address.

Suppose you are typing "NewYork" into cell A2 and you want the address to
appear in B2. In B2, you need a formula such as
=VLOOKUP(A2,Sheet2!A1:B20,2,0)
where Sheet2!A1:B20 is a reference to the range containing the list of
offices and addresses.

 

[Guest]

-----Original Message-----
I have a column in a worksheet called "office" and one called "address". a
list of each office address is in another sheet like so:

Office Location
New York Address


How can i setup a function to where if New York is typed into a cell in the
office column that the address cell in that row will be automatically
populated with the appropriate address.

=IF(A1="New York","123 test avenue","Error")

.

 

[Gord Dibben]

In addition to Paul's suggestion, use Data>Validation to set up a drop-down
list in your Office cells on first sheet.

Saves typing<g>

Gord Dibben Excel MVP

 

[Jeff Glock]

Use the VLOOKUP function.

1st) Sort the list of addresses by the office in
ascending order. (VLOOKUP only works if the table it is
searching is sorted) Then define a name for the range of
cells on the list of office addresses. Highlight the cells
in the list. Then from the menu bar choose "Insert"
then "Name" then "Define". In the dialoge box that
appears enter a name at the top, for example "AddressList".

2nd) On your new sheet, lets presume that the first cell
in the column "office" is cell "A2" and the cell where you
want the address to appear is "B2"

The formula for cell "B2" is as follows;

=VLOOKUP(A2,AddressList,2,false)

The "2" in this formula refrences the second column of the
named range "AddressList". "false" indicates that only an
exact match is acceptable, otherwise the function will
return the adress of which ever office is closest to the
value you key into the "A2" cell.