#1   Report Post  
KG
 
Posts: n/a
Default 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   Report Post  
Alan Beban
 
Posts: n/a
Default

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   Report Post  
KG
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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
.

  #8   Report Post  
JMB
 
Posts: n/a
Default

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   Report Post  
jiwolf
 
Posts: n/a
Default

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   Report Post  
RagDyeR
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
basic pie chart question KayR Charts and Charting in Excel 4 January 23rd 05 09:16 PM
Need help with modifying VLookUp Tom Excel Discussion (Misc queries) 4 December 2nd 04 01:44 AM
Vlookup with VBA Jeff Excel Discussion (Misc queries) 8 December 1st 04 03:41 PM
Vlookup returns incorrect match Smichaud Excel Discussion (Misc queries) 2 November 30th 04 11:51 AM
Vlookup / Objects help XL2003 Steve Jones Excel Discussion (Misc queries) 0 November 29th 04 06:01 PM


All times are GMT +1. The time now is 02:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"