vlookup Function

F

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.

D

Dave Peterson

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.

J

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.