How to maintain references when copying lookup formula
 

Hi and thanks for reading this post.

I'm a new user with Excel 2007 although am MOUS with Excel 2000. My
problem is that I have a simple formula that I'm using to lookup staff
names from another sheet. The formula is;

=LOOKUP(B3,Data!B3:B17,Data!C3:C17)

All I need to do is to copy this formula down the column, but I need
the formula to retain the lookup cell references. At the moment if I
copy the formula, I get an increment like this;

=LOOKUP(B4,Data!B4:B18,Data!C4:C18)

This returns an 'NA#' error because the lookup vector has changed
along with the reference.

How can I get excel to return a formula which looks like this?;

=LOOKUP(B4,Data!B3:B17,Data!C3:C17)

Thanks in advance.

How to maintain references when copying lookup formula
 

Hi Richard,

You could make your look up an absolute reference...

=LOOKUP(B3,Data!$B$3:$B$17,Data!$C$3:$C$17)

Hope this helps,

Gav.

" wrote:

Hi and thanks for reading this post.

I'm a new user with Excel 2007 although am MOUS with Excel 2000. My
problem is that I have a simple formula that I'm using to lookup staff
names from another sheet. The formula is;

=LOOKUP(B3,Data!B3:B17,Data!C3:C17)

All I need to do is to copy this formula down the column, but I need
the formula to retain the lookup cell references. At the moment if I
copy the formula, I get an increment like this;

=LOOKUP(B4,Data!B4:B18,Data!C4:C18)

This returns an 'NA#' error because the lookup vector has changed
along with the reference.

How can I get excel to return a formula which looks like this?;

=LOOKUP(B4,Data!B3:B17,Data!C3:C17)

Thanks in advance.

How to maintain references when copying lookup formula
 

Of course. Absolute references are the answer. Many thanks!




On Jan 3, 10:46 am, Gav123 wrote:
Hi Richard,

You could make your look up an absolute reference...

=LOOKUP(B3,Data!$B$3:$B$17,Data!$C$3:$C$17)

Hope this helps,

Gav.

" wrote:
Hi and thanks for reading this post.

I'm a new user with Excel 2007 although am MOUS with Excel 2000. My
problem is that I have a simple formula that I'm using to lookup staff
names from another sheet. The formula is;

=LOOKUP(B3,Data!B3:B17,Data!C3:C17)

All I need to do is to copy this formula down the column, but I need
the formula to retain the lookup cell references. At the moment if I
copy the formula, I get an increment like this;

=LOOKUP(B4,Data!B4:B18,Data!C4:C18)

This returns an 'NA#' error because the lookup vector has changed
along with the reference.

How can I get excel to return a formula which looks like this?;

=LOOKUP(B4,Data!B3:B17,Data!C3:C17)

Thanks in advance.