Column values still changing even with $ in front

[Sprackers]

Hi there

Ive written my Vlookup Formula including the $ in front of my arrays to freeze the values, but they still change when I drag the formula down.???

Any help apprecited

Sprackers

 

[steve the fish]

A dollar in front will freeze the columns:

=$A1

A dollar in the middle freezes the row:

=A$1

Dollar both sides freezes both:

=$A$1

Try dragging them about and seeing the results.

 

[Joe4]

An alternative:
Name your range and then use the named range in your VLOOKUP formulas. Then you do not need to mess with range references.

If you unsure how to do that, see this: VLOOKUP with NAMED RANGE - YouTube

 

[Sprackers]

An alternative:
Name your range and then use the named range in your VLOOKUP formulas. Then you do not need to mess with range references.

If you unsure how to do that, see this: VLOOKUP with NAMED RANGE - YouTube

OK the $ sign doesnt work it keeps on changing the vlaues and the name on the list after following the tutorial doesnt pick it up.

 

[Sprackers]

Im trying to drag my Vlookup formula down. the original has $ in front to the array columns but keep on changing them.

Ive tried to give the data set a name but when I try to insert it into my formula it doesn pick it up.

BUT if I go to a random cell and pres = then the first letters of the name of the array it DOES pick it up.

Both very strange.

 

[Sprackers]

Here is my original formula if this helps?

=VLOOKUP(E3,'All resources and ID 1115'!$A2:$B1112,2,FALSE)

When I drag it down the $A2 goes to $A3 then $A4 etc......and the $B112 goes to $B113 etc.....

 

[Rick Rothstein]

Here is my original formula if this helps?

=VLOOKUP(E3,'All resources and ID 1115'!$A2:$B1112,2,FALSE)

When I drag it down the $A2 goes to $A3 then $A4 etc......and the $B112 goes to $B113 etc.....

That is because you froze the column against dragging across... but you are not dragging across, you are dragging down, so you have to freeze the rows...

=VLOOKUP(E3,'All resources and ID 1115'!A$2:B$1112,2,FALSE)

Not sure about the last row number because your formula uses 1112 but your written description uses 112.

 

[steve the fish]

Post number 2 will explain how to get that formula to work dragging down.

 

[Joe4]

OK the $ sign doesnt work it keeps on changing the vlaues and the name on the list after following the tutorial doesnt pick it up.

Did you watch the video I linked to?
That has no impact on using named ranges, if you set it up correctly.

If you select the whole range you want to include in your VLOOKUP, and then name it something like "MyRange", then when you write your VLOOKUP formula, you would write it like:
=VLOOKUP(E3,MyRange,2,FALSE)

You can then drag it to wherever you like; like the value "MyRange" is not going to change!

 

[Sprackers]

Brilliant chaps. Thanks alot although Im still confused about why my range name isn't picked up when i type the formula. Still, I can live without that for the time being.

Many thanks again

Sprackers