Problem with Vlookup array selection
 

So I'm creating a vlookup to look at an array of 500 values and return
to me the value (so I can compare a list of 3000 values to find which
500 are in the array). I've completed many vlookups over the years and
have always been able to just copy the first one and paste it down the
list of 3000 values to look up in the 500 value array and it would keep
looking at the same array. Well now all of a sudden this morning, it
decides to increment my array every step down. What the heck is going
on? For example, it should go like this....

=VLOOKUP(A2,'sheet2'!A1:A510,1,FALSE)
=VLOOKUP(A3,'sheet2'!A1:A510,1,FALSE)
=VLOOKUP(A4,'sheet2'!A1:A510,1,FALSE)
=VLOOKUP(A5,'sheet2'!A1:A510,1,FALSE)

and so forth, but instead, when I'm pasting or pasting special
(formulas), I get this...

=VLOOKUP(A2,'sheet2'!A1:A510,1,FALSE)
=VLOOKUP(A3,'sheet2'!A2:A511,1,FALSE)
=VLOOKUP(A4,'sheet2'!A3:A512,1,FALSE)
=VLOOKUP(A5,'sheet2'!A4:A513,1,FALSE)

What do I need to change for Excel to paste the formula like the first
set of examples?

Thanks,
-Scott H.

Problem with Vlookup array selection
 

That's why I always use named ranges for my vlookups (whenever reasonable to
do so), as described he
http://www.officearticles.com/excel/...soft_excel.htm
But you need to reference absolutely your table:
=VLOOKUP(A2,'sheet2'!$A$1:$A$510,1,FALSE)
:)
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Scott269" wrote in message
oups.com...
So I'm creating a vlookup to look at an array of 500 values and return
to me the value (so I can compare a list of 3000 values to find which
500 are in the array). I've completed many vlookups over the years and
have always been able to just copy the first one and paste it down the
list of 3000 values to look up in the 500 value array and it would keep
looking at the same array. Well now all of a sudden this morning, it
decides to increment my array every step down. What the heck is going
on? For example, it should go like this....

=VLOOKUP(A2,'sheet2'!A1:A510,1,FALSE)
=VLOOKUP(A3,'sheet2'!A1:A510,1,FALSE)
=VLOOKUP(A4,'sheet2'!A1:A510,1,FALSE)
=VLOOKUP(A5,'sheet2'!A1:A510,1,FALSE)

and so forth, but instead, when I'm pasting or pasting special
(formulas), I get this...

=VLOOKUP(A2,'sheet2'!A1:A510,1,FALSE)
=VLOOKUP(A3,'sheet2'!A2:A511,1,FALSE)
=VLOOKUP(A4,'sheet2'!A3:A512,1,FALSE)
=VLOOKUP(A5,'sheet2'!A4:A513,1,FALSE)

What do I need to change for Excel to paste the formula like the first
set of examples?

Thanks,
-Scott H.

Problem with Vlookup array selection
 

Anne Troy wrote:
That's why I always use named ranges for my vlookups (whenever reasonable to
do so), as described he
http://www.officearticles.com/excel/...soft_excel.htm
But you need to reference absolutely your table:
=VLOOKUP(A2,'sheet2'!$A$1:$A$510,1,FALSE)
:)

Ahh, the dollar signs for absolute, that's what I needed. Thanks!