How to stop parts of a formula from being incremented when using the fill handle

[haz13]

I would like to know how a stop part of my formula from incrementin
when using the fill handle.
Here is the formula =VLOOKUP(F2,A2:B38,2,FALSE).

I want the F2 cell to increment down which is currently happening, bu
not the A2:B38 bit (this part is also incrementing but I don't want i
to). I am assuming there will be a prefix or something i need to pu
infront of the A2:B38 bit.

Thanks in advance, haz1

 

[Ragdyer]

What you're referring to is 'relative' and 'absolute' cell references.
Row and column references in a cell address are treated separately,
where the row can increment and not the column ... and vice-versa.
Relative: both increment = A1
Absolute: both remain unchanged = $A$1
Absolute row = A$1
Absolute column = $A1

In your example:

=VLOOKUP(F2,$A$2:$B$38,2,FALSE)

 

[Ragdyer]

FWIW,

Since you're copying *down*, there's really no need to make both references
absolute, since the column will remain unchanged *anyway*.

=VLOOKUP(F2,A$2:B$38,2,FALSE)

However, most folks tend to use the absolutes for both as a habit.
And also, it's easier to accomplish this by selecting the cell reference in
the formula bar and simply hitting <F4> which will make the entire address
absolute.
Take note though, each hit of <F4> will give a different relative, absolute
reference, *in a repeating loop*.

If you select the *entire* formula in the formula bar, each hit of <F4> will
change *every* cell reference in the whole formula at the same time.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

What you're referring to is 'relative' and 'absolute' cell references.
Row and column references in a cell address are treated separately,
where the row can increment and not the column ... and vice-versa.
Relative: both increment = A1
Absolute: both remain unchanged = $A$1
Absolute row = A$1
Absolute column = $A1

In your example:

=VLOOKUP(F2,$A$2:$B$38,2,FALSE)

--
HTH,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -

 

[Dave Peterson]

I don't like to put my lookup table on the same sheet as other data (if
possible).

I have to be careful inserting or deleting rows or columns.

I like to dedicate a worksheet to that table. And if the worksheet is dedicated
to that function, I can use a formula like:

=vlookup(f2,sheet2!a:b,2,false)

And since I'm using the whole column, I don't have to worry abour the range
changing rows.