How to maintain references when copying lookup formula

R

richard

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.
 
G

Gav123

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top