Copying a formula over multiple cells

[Randomhero180]

Hey All,

I am doing a VLOOKUP and i need to copy it over 2000 cells, problem is when I copy it, the array changes, not just the Lookup_value

Example:

=VLOOKUP(A19,Sheet2!A2:B408,2,0)

this is the first cells equation and when i try to copy it to the next cell or do a fill it changes it to:

=VLOOKUP(A20,Sheet2!A3:B409,2,0)

Obviously the Lookup_value is doing the right thing, but the table array is not. and I know i have done this before, not sure why it is doing it now.

any help would be great. Thanks!

RH

 

[Jonmo1]

Use Absolute references...

=VLOOKUP(A19,Sheet2!A$2:B$408,2,0)

Hope that helps.

 

[Randomhero180]

I have tried this, the formula looks right on every cell but everything returns #N/A and there should be values in 50% of the cells, and they do change when i take the absolute reference out on the cells i know should have a value, I just don't want to have to do this on 1000+ cells.

 

[Jonmo1]

#N/A will have nothing to do with the $ in the formula.

Is the correct range displayed in the formula that returns #N/A ?

#N/A simply means that there was no match found for the lookup value.

If the lookup value is Text, check for correct spelling, and extra spaces
In both the lookup value, and the 1st column of the lookup range.

If the lookup value is Number, then it's likely your numbers are not really numbers,
But they are "Numbers stored as text"

What does this return?
=ISNUMBER(A20)
and
=ISNUMBER(Sheet2!A3)

Were A20 and sheet2!A3 are numbers you believe to be a match..

 

[Randomhero180]

I dont know what to say the reference did affect it. This is what I did to fix it.

-put the absolute reference in and copied it to all cells
-switched to show formulas
-did a find all A$2 and replaced all with A2 (did the same for B408)
-got rid of show formula

and everything is there. I know it doesn't make sense but the absolute reference was doing something weird.