LOOKUP a phone number?

[Laura]

Is this possible?
I would like to create a LOOKUP formula that would tell me the phone number
if I put in the name.

For instance, I have one worksheet that looks like this:

Name Home Cell
Ann 555-1234 555-0000
Bob 555-5678 555-1111
Claire 555-9012 555-2222

On another worksheet, I want to set-up a formula so that if I enter a name
in A2, the home # will be generated in B2 and the cell # will be generated in
C2.

Any ideas?

Thanks.

 

[Biff]

Hi!

Try this:

This table is on Sheet2 in the range A2:C4 -

Ann 555-1234 555-0000
Bob 555-5678 555-1111
Claire 555-9012 555-2222

Sheet1:

A2 = Ann

Enter this formula in B2 and copy across to C2:

=IF($A2="","",VLOOKUP($A2,Sheet2!$A2:$C4,COLUMNS($A:B),0))

Biff

 

[Jim Thomlinson]

You probably want a vlookup formula, something like this...

=vlookup(A2, sheet1!$A$1:$C$100, 2, false)

 

[Laura]

that is SO AWESOME! THANK YOU!

Now, one more thing.... can you tell me what I can add to the formula so if
one of the phone number cells is blank on the lookup table the other one will
be blank also instead of showing a "0"?

thanks again.

 

[Biff]

Enter in B2 and copy across to C2:

=IF($A2="","",IF(VLOOKUP($A2,Sheet2!$A2:$C4,COLUMNS($A:B),0)="","",VLOOKUP($A2,Sheet2!$A2:$C4,COLUMNS($A:B),0)))

Biff

 

[Laura]

THANK YOU once again!

OK, so I am going to try and complicate the situation. Let's say that the
lookup table belongs to a syncronized list in SharePoint. If the contact
information increases, then the lookup table range will increase also. In
this case, is there any way to designate the look-up table as the entire list
rather than by a specific cell range? Or if I decide to leave it as a cell
range, will it automatically update the lookup range in the formula?

 

[Biff]

You can do a couple of things in that case:

Just use entire columns as the range references for the table:

=IF($A2="","",IF(VLOOKUP($A2,Sheet2!$A:$C,COLUMNS($A:B),0)="","",VLOOKUP($A2,Sheet2!$A:$C,COLUMNS($A:B),0)))

Or, you can make the lookup table a dynamic range:

http://contextures.com/xlNames01.html#Dynamic

Then you would use the dynamic range name:

=IF($A2="","",IF(VLOOKUP($A2,Table,COLUMNS($A:B),0)="","",VLOOKUP($A2,Table,COLUMNS($A:B),0)))

Biff