I have two separate sheets in my workbook, and I want to return data onto sheet 1 "DATA" from column 12 on sheet 2 "WEIGHTS" by matching values in the lefthand columns on both sheets. I'm new-ish to Excel and I've only successfully used a Vlookup once. I know my formula is correct because it is working on the first row.

=VLOOKUP(A2,WEIGHTS!1:65536,12)

But when I go to copy it down, I get #REF! and the formula changes to this.

=VLOOKUP(A3,WEIGHTS!#REF!,12)

I know it has to do with selecting the entire sheet and the formula wanting to add a row onto 65536, but I have no idea how to fix it. How do I select a column off the second sheet for the table array?

I figured it out myself but can't figure out how to delete this post. The problem was that I hadn't sorted my WEIGHTS column in ascending order. *facepalm*

kristawoods brought next idea :
I have two separate sheets in my workbook, and I want to return data
onto sheet 1 "DATA" from column 12 on sheet 2 "WEIGHTS" by matching
values in the lefthand columns on both sheets. I'm new-ish to Excel and
I've only successfully used a Vlookup once. I know my formula is
correct because it is working on the first row.

=VLOOKUP(A2,WEIGHTS!1:65536,12)

But when I go to copy it down, I get #REF! and the formula changes to
this.

=VLOOKUP(A3,WEIGHTS!#REF!,12)

I know it has to do with selecting the entire sheet and the formula
wanting to add a row onto 65536, but I have no idea how to fix it. How
do I select a column off the second sheet for the table array?

You're running off the bottom of the sheet when you copy down. Yoe need
to use fully absolute refs for the rows...

=VLOOKUP(A2,WEIGHTS!$1:$65536,12)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion