Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP question
I have a 10 year discounted cash flow schedule where the discount factor is a
variable chosen by the workbook user. I have also set up a discount factor table so that the appropriate discounting factors can be inserted under each column. The formula for year 1 (=Column 1) of the cash flow schedule is: =VLOOKUP($C$27,$C$63:$N$82,2) I copied the formula across the row and then manually changed the 3rd argument of the formula to 3, 4, 5 and so on. This was not a huge editing job but, in anticipation of future schedules with 20 columns, I'm wondering if there is a better way to write this formula, eliminating the need for manual edits. |
#3
|
|||
|
|||
KG wrote:
I have a 10 year discounted cash flow schedule where the discount factor is a variable chosen by the workbook user. I have also set up a discount factor table so that the appropriate discounting factors can be inserted under each column. The formula for year 1 (=Column 1) of the cash flow schedule is: =VLOOKUP($C$27,$C$63:$N$82,2) I copied the formula across the row and then manually changed the 3rd argument of the formula to 3, 4, 5 and so on. This was not a huge editing job but, in anticipation of future schedules with 20 columns, I'm wondering if there is a better way to write this formula, eliminating the need for manual edits. Change the ,2 to ,COLUMN(B1) Alan Beban |
#4
|
|||
|
|||
Thank you (and thanks to Don Guillett, who had the same suggestion). Using
Column(b1) instead of ,2 does the trick "Alan Beban" wrote: KG wrote: I have a 10 year discounted cash flow schedule where the discount factor is a variable chosen by the workbook user. I have also set up a discount factor table so that the appropriate discounting factors can be inserted under each column. The formula for year 1 (=Column 1) of the cash flow schedule is: =VLOOKUP($C$27,$C$63:$N$82,2) I copied the formula across the row and then manually changed the 3rd argument of the formula to 3, 4, 5 and so on. This was not a huge editing job but, in anticipation of future schedules with 20 columns, I'm wondering if there is a better way to write this formula, eliminating the need for manual edits. Change the ,2 to ,COLUMN(B1) Alan Beban |
#6
|
|||
|
|||
Go to Edit-Find & write in the "find" field ,2 and then
in the replace field ,COLUMN(B1) and press "replace all" Rgds, Immu -----Original Message----- KG wrote: I have a 10 year discounted cash flow schedule where the discount factor is a variable chosen by the workbook user. I have also set up a discount factor table so that the appropriate discounting factors can be inserted under each column. The formula for year 1 (=Column 1) of the cash flow schedule is: =VLOOKUP($C$27,$C$63:$N$82,2) I copied the formula across the row and then manually changed the 3rd argument of the formula to 3, 4, 5 and so on. This was not a huge editing job but, in anticipation of future schedules with 20 columns, I'm wondering if there is a better way to write this formula, eliminating the need for manual edits. Change the ,2 to ,COLUMN(B1) Alan Beban . |
#7
|
|||
|
|||
|
#8
|
|||
|
|||
If both tables have the same column headers you could use the MATCH function
to specify the column number VLOOKUP should return. "KG" wrote: I have a 10 year discounted cash flow schedule where the discount factor is a variable chosen by the workbook user. I have also set up a discount factor table so that the appropriate discounting factors can be inserted under each column. The formula for year 1 (=Column 1) of the cash flow schedule is: =VLOOKUP($C$27,$C$63:$N$82,2) I copied the formula across the row and then manually changed the 3rd argument of the formula to 3, 4, 5 and so on. This was not a huge editing job but, in anticipation of future schedules with 20 columns, I'm wondering if there is a better way to write this formula, eliminating the need for manual edits. |
#9
|
|||
|
|||
yes there is. to make the 3rd argument dynamic, you could set up a small
array. For example, in cell A1 enter the value 1. In cell B1 enter the value 2 and so on up to T1. Change your formula to read =VLOOKUP($C$27,$C$63:$N$82,A1) then copy across the columns. " "KG" wrote: I have a 10 year discounted cash flow schedule where the discount factor is a variable chosen by the workbook user. I have also set up a discount factor table so that the appropriate discounting factors can be inserted under each column. The formula for year 1 (=Column 1) of the cash flow schedule is: =VLOOKUP($C$27,$C$63:$N$82,2) I copied the formula across the row and then manually changed the 3rd argument of the formula to 3, 4, 5 and so on. This was not a huge editing job but, in anticipation of future schedules with 20 columns, I'm wondering if there is a better way to write this formula, eliminating the need for manual edits. |
#10
|
|||
|
|||
Cannot see the OP in my O.E., and I'm responding through JMB's reply:
KG, To have the column index *automatically* increment as you drag along a row (across columns), you can use the Column() function. Enter =COLUMN(D:D) in any cell, and you'll return a "4", the number for Column D. Drag to copy this formula across the columns, and you'll see the numbers increment as the relative references automatically adjust the column address. So, for example, in the formula you posted, try this: =VLOOKUP($C$27,$C$63:$N$82,COLUMN(B:B)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "JMB" wrote in message ... If both tables have the same column headers you could use the MATCH function to specify the column number VLOOKUP should return. "KG" wrote: I have a 10 year discounted cash flow schedule where the discount factor is a variable chosen by the workbook user. I have also set up a discount factor table so that the appropriate discounting factors can be inserted under each column. The formula for year 1 (=Column 1) of the cash flow schedule is: =VLOOKUP($C$27,$C$63:$N$82,2) I copied the formula across the row and then manually changed the 3rd argument of the formula to 3, 4, 5 and so on. This was not a huge editing job but, in anticipation of future schedules with 20 columns, I'm wondering if there is a better way to write this formula, eliminating the need for manual edits. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
basic pie chart question | Charts and Charting in Excel | |||
Need help with modifying VLookUp | Excel Discussion (Misc queries) | |||
Vlookup with VBA | Excel Discussion (Misc queries) | |||
Vlookup returns incorrect match | Excel Discussion (Misc queries) | |||
Vlookup / Objects help XL2003 | Excel Discussion (Misc queries) |