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

"Laura" wrote in message
...
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" wrote:

Enter in B2 and copy across to C2:

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

Biff

"Laura" wrote in message
...
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" wrote:

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

"Laura" wrote in message
...
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.