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.

try playing around with column() or column(a1) instead of ,2

--
Don Guillett
SalesAid Software

"KG" wrote in message
...
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.

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

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

glad to help

--
Don Guillett
SalesAid Software

"KG" wrote in message
...
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

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
.

Won't that make all then column(b1)?

--
Don Guillett
SalesAid Software

wrote in message
...
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
.

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.

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.

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.