vlookup Function

Farhan · External Since: Nov 07, 2009 Posts: 1

Hello

=VLOOKUP(F2,'Payment Method'!A1:B10,2,FALSE)
by this formula i want to look the value given in the cell "F2" in the table
A1:B10 on the sheet "Payment Method". A1:B10 Table is fixed So i dont want
any change in the range of this table (A1:b10). When i copy this formula to
the very next down cell of the same column, it becomes
=VLOOKUP(F3,'Payment Method'!A2:B11,2,FALSE). Table Range is changed which
is not required Because i want to look the value only in the following range
(A1:B10).
I want that when i copy the this formula to very next down cell, its table
value (A1:b10) Shouldnt change whenever the value of the "F" cell should be
changed. it should be from F2 to F10 if i copy it in next 10 cells.

Jacob Skaria · External Since: Mar 04, 2009 Posts: 441

Hi Farhan

Use absolute referencing for the table array

=VLOOKUP(F2,'Payment Method'!$A$1:$B$10,2,FALSE)
OR
=VLOOKUP(F2,'Payment Method'!$A$1:$B$10,2,0)
OR 'since you are only copying down
=VLOOKUP(F2,'Payment Method'!A$1:B$10,2,0)

Below are the different reference styles.
A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.
--
If this post helps click Yes
---------------
Jacob Skaria

"Farhan" wrote:

> Hello
>
> =VLOOKUP(F2,'Payment Method'!A1:B10,2,FALSE)
> by this formula i want to look the value given in the cell "F2" in the table
> A1:B10 on the sheet "Payment Method". A1:B10 Table is fixed So i dont want
> any change in the range of this table (A1:b10). When i copy this formula to
> the very next down cell of the same column, it becomes
> =VLOOKUP(F3,'Payment Method'!A2:B11,2,FALSE). Table Range is changed which
> is not required Because i want to look the value only in the following range
> (A1:B10).
> I want that when i copy the this formula to very next down cell, its table
> value (A1:b10) Shouldnt change whenever the value of the "F" cell should be
> changed. it should be from F2 to F10 if i copy it in next 10 cells.

Dave Peterson · External Since: Jul 08, 2005 Posts: 16047

Excel allows you to use relative addresses and absolute addresses--this
difference is shown when you copy the formula:

=VLOOKUP(F2,'Payment Method'!$A$1:$B$10,2,FALSE)

Those $ in $a$1:$b$10 mean that that portion (row or column) should not be
changed when the formula is copied elsewhere.

Farhan wrote:
>
> Hello
>
> =VLOOKUP(F2,'Payment Method'!A1:B10,2,FALSE)
> by this formula i want to look the value given in the cell "F2" in the table
> A1:B10 on the sheet "Payment Method". A1:B10 Table is fixed So i dont want
> any change in the range of this table (A1:b10). When i copy this formula to
> the very next down cell of the same column, it becomes
> =VLOOKUP(F3,'Payment Method'!A2:B11,2,FALSE). Table Range is changed which
> is not required Because i want to look the value only in the following range
> (A1:B10).
> I want that when i copy the this formula to very next down cell, its table
> value (A1:b10) Shouldnt change whenever the value of the "F" cell should be
> changed. it should be from F2 to F10 if i copy it in next 10 cells.

--

Dave Peterson