vlookup Function

[Farhan]

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]

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.

 

[Jacob Skaria]

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.