VLOOKUP question

[Guest]

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.

 

[Don Guillett]

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

 

[Alan Beban]

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

 

[Guest]

Thank you (and thanks to Don Guillett, who had the same suggestion). Using
Column(b1) instead of ,2 does the trick

 

[Don Guillett]

glad to help

 

[Guest]

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-----

 

[Don Guillett]

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

 

[Guest]

If both tables have the same column headers you could use the MATCH function
to specify the column number VLOOKUP should return.

 

[jiwolf]

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.

 

[RagDyeR]

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)
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!
==============================================




If both tables have the same column headers you could use the MATCH function
to specify the column number VLOOKUP should return.