Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup Function
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup Function
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup Function
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Embedded If Function in a Vlookup Function | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |