VLookup using 2 worksheets, whole column reference

R

ROSIE

I have two pieces of similar data, one old (sheet 1) and one with new
information (sheet 2). Sheet 1 will contain some information I want to bring
across into Sheet 2 if the identifying references match. I have been using:

VLOOKUP(A1,'sheet 1'!A1:CC3000,81,0)

A1 is the common link with an identifying number. I'm asking that if the
identifying number existed on the previous data (sheet 1) to then return the
value shown in column 81.

This only works if the identifying number is on the same row in sheet 1 as
the formula I'm typing in sheet 2. I want the formula in sheet 2 to search
all of column 1, ie. A1:A3000. At the moment it's only comparing it's exact
replica on sheet 1.

Thanks.
 
B

bpeltzer

This is set up to indeed search in Sheet1!A1:A3000. But if you copied this
formula, as you posted it here, to subsequent rows, the table range would
change. If sheet2 has more data, then you would very likely slip past the
matching row on sheet1 and then fail to match. Use absolute references in
your table range to prevent that problem: =vlookup(a1,'sheet
1'!$A$1:$CC$3000,81,0)
I'm usually even lazier and just leave off the row qualifiers:
=vlookup(a1,'sheet 1'!a;c,81,0)
--Bruce
 
Top