I have a about 2000 rows and i need them to reference sheet 2 for
value.
When i drag the formula down excel moves the array as well...

here is what i want in each cell:
=vlookup(b2,sheet2!a1:b2000,2)
=vlookup(b3,sheet2!a1:b2000,2)
=vlookup(b4,sheet2!a1:b2000,2)
=vlookup(b5,sheet2!a1:b2000,2)
and so on

but excel does this...
=vlookup(b2,sheet2!a1:b2001,2)
=vlookup(b3,sheet2!a2:b2002,2)
=vlookup(b4,sheet2!a3:b2003,2)
=vlookup(b5,sheet2!a4:b2004,2)

What am I doing wrong?

thank you


--
rainxking
------------------------------------------------------------------------
rainxking's Profile: http://www.excelforum.com/member.php...fo&userid=9431
View this thread: http://www.excelforum.com/showthread...hreadid=381811

Hi rainxking,

You just forgot to establish an absolute reference to your lookup table,
which you can do using the $ sign, like this:

=vlookup(b2,sheet2!$a$1:$b$2000,2)

Try copying now.

Regards,
KL


"rainxking" wrote in
message ...

I have a about 2000 rows and i need them to reference sheet 2 for
value.
When i drag the formula down excel moves the array as well...

here is what i want in each cell:
=vlookup(b2,sheet2!a1:b2000,2)
=vlookup(b3,sheet2!a1:b2000,2)
=vlookup(b4,sheet2!a1:b2000,2)
=vlookup(b5,sheet2!a1:b2000,2)
and so on

but excel does this...
=vlookup(b2,sheet2!a1:b2001,2)
=vlookup(b3,sheet2!a2:b2002,2)
=vlookup(b4,sheet2!a3:b2003,2)
=vlookup(b5,sheet2!a4:b2004,2)

What am I doing wrong?

thank you


--
rainxking
------------------------------------------------------------------------
rainxking's Profile:
http://www.excelforum.com/member.php...fo&userid=9431
View this thread: http://www.excelforum.com/showthread...hreadid=381811