VLOOKUP Formula

[brettponch]

First an overview of what I'm attempting to accomplish. I am creating a spreadsheet for my team, and would like to have a drop down in columnm "A" with the names of the team members. Based on the name selected, I would like to have their contact information (phone, fax, email) populate automatically in other cells on the same sheet. I followed the advice in the link below......

http://www.mrexcel.com/archive/Dates/24026.html

The problem being that when you select a name from the drop down, it doesn't automatically populate the corresponding cell with the suggested formula in it.

If you select the corresponding cell (after having chosen the name), then click on the formula bar followed by return, it works, but that kind of defeats the purpose of what I'm trying to accomplish.

I have to many names to use an "if" formula easily, and don't want to debug something that long and complex everytime somebody comes into, or leaves the team. Any help that can be given would be greatly appreciated.

 

[shemayisroel]

brettponch Welcome to the Board,

Below is some sample data, change the name value in A2 and it will return their details.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0">NAME</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">PHONE</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">FAX</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">EMAIL</TD><TD> </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ff9900">NAME</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">PHONE</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">FAX</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">EMAIL</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: center">321</TD><TD> </TD><TD style="TEXT-ALIGN: center">333@hotmail.com</TD><TD> </TD><TD>Noah</TD><TD style="TEXT-ALIGN: center">123</TD><TD> </TD><TD style="COLOR: #0000ff; TEXT-ALIGN: center; TEXT-DECORATION: underline">aaa@gmail.com</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Mary</TD><TD style="TEXT-ALIGN: center">456</TD><TD style="TEXT-ALIGN: center">555</TD><TD style="COLOR: #0000ff; TEXT-ALIGN: center; TEXT-DECORATION: underline">123@gmail.com</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Joseph</TD><TD> </TD><TD style="TEXT-ALIGN: center">333</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Sarah</TD><TD style="TEXT-ALIGN: center">321</TD><TD> </TD><TD style="COLOR: #0000ff; TEXT-ALIGN: center; TEXT-DECORATION: underline">333@hotmail.com</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Samson</TD><TD style="TEXT-ALIGN: center">784</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Joshua</TD><TD style="TEXT-ALIGN: center">653</TD><TD style="TEXT-ALIGN: center">777</TD><TD style="COLOR: #0000ff; TEXT-ALIGN: center; TEXT-DECORATION: underline">noemail@yahoo.com</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=IF(VLOOKUP($A$2,$F$2:$I$7,2,FALSE)="","",VLOOKUP($A$2,$F$2:$I$7,2,FALSE))</TD></TR><TR><TD>C2</TD><TD>=IF(VLOOKUP($A$2,$F$2:$I$7,3,FALSE)="","",VLOOKUP($A$2,$F$2:$I$7,3,FALSE))</TD></TR><TR><TD>D2</TD><TD>=IF(VLOOKUP($A$2,$F$2:$I$7,4,FALSE)="","",VLOOKUP($A$2,$F$2:$I$7,4,FALSE))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[brettponch]

I assume that I can bury the contact information further down on the sheet? Do I need to change the information in the formula if so? What in the formula is actually pointing to the table with all the contact info?

 

[xenou]

Sometimes just putting a volatile formula on the page somewhere even in one cell helps trigger calculation... (?) not sure. But try putting =Now() in some inconspicuous cell on the same sheet. Not sure though.

 

[xenou]

Note that in the example from shemayisroel:
$F$2:$I$7

is the table reference

 

[shemayisroel]

I assume that I can bury the contact information further down on the sheet? Do I need to change the information in the formula if so? What in the formula is actually pointing to the table with all the contact info?

If you bury the contact information (i'm assuming your table reference info) further down the page, you will need to change the table reference.

 

[brettponch]

I'm having the same issue that I did with the last formula. The only way that it calculates and populates the cell is if you click on the formula bar and hit return. Is there something I need to do in order for that to "auto calculate"? I tried the suggestion of "=Now()" in a random cell, but that didn't seem to work. Any suggestions or guidance would be greatly appreciated.

 

[xenou]

Does a simpler formula work?

If dropdown is in A1, then in B1 type =A1, and have your lookup take the value from B1 rather than A1 (?) not sure though.

 

[Barry Katcher]

Go to Tools/Options/Calculation and make sure Automatic is selected.