Vlookup Help

[tomehta]

Hello
In attached excel screen shots, in Col I (sheet 1), I want Col C value (from sheet 2), similarly in Col P (sheet 1) want Col D value( from Sheet 2). I am trying with variations of Columns function but not able to crack. I want to create formula in col B sheet 1 and drag it. I dont want to manually update formula in Col I and Col P ( and hence forth). Any help will be greatly appreciated.

 

[tomehta]

awesome this is working included the reference sheet from your formula.

=INDEX(AppView!2:2,,COLUMNS($B2:B2)*3.5-3.5+5) - INDEX(AppView!2:2,,COLUMNS($B2:B2)*3.5-3.5+9)
=INDEX(AppView!2:2,,COLUMNS($C2:C2)*3.5-3.5+6) - INDEX(AppView!2:2,,COLUMNS($C2:C2)*3.5-3.5+10)

I want to restate my understanding, now we are using the full rows from the reference sheet rather than the range.

One query,

BAU Actl Aug-14 is going to be in $DQ12 which is outside of the range that you used in the formula

Columns function is returning 117 or may be 113 , why excel is looking into DQ column..

Once again, thanks for your help,
Next I am diving into charting these numbers

 

[jasonb75]

Excel was not looking to DQ because the INDEX range only went to DN, but the formula was correctly calculating that the result should be coming from DQ, hence the error.
The calculation worked out that it would have been the 113th column in the INDEX range, however $I2:$DN2 only contains 110 columns.

Changing the formula to use full rows instead of a limited number of columns prevents the same error from occurring again, however it requires a slightly different calculation to work correctly, as you will see, the +1 at the end of the formula has been changed to the number of the column that holds the result of the first formula.

 

[tomehta]

got it, thanks , have a nice long weekend

 

[tomehta]

Hello , not able to figure out the steps again, reference sheet moves 11 steps and refering sheets move 7 steps, what shud be the columns formula using index..

Forecast DASH v13.0.xlsx
	H
3	5.0
4	9.0
5	15.0
6	20.0
7	16.0
ResourceView

Cell Formulas
Range	Formula
H3:H7	H3	=INDEX(PrjctForecast!3:3,,COLUMNS($Q2:Q2)*11/7-11/7+17)

Forecast DASH v13.0.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
24	20	5	5	5	0	5	0	0	0	0	0	20
PrjctForecast

Cell Formulas
Range	Formula
Q24,AB24	Q24	=SUM(G24:P24)

 

[jasonb75]

Looks fine to me, what's wrong with the results?

 

[tomehta]

I am getting 34 rather than 28 as column

 

[jasonb75]

Using the formula from your post, I get 17, 28, 39, 50, 61, etc as I copy the formula right to columns H, O, V, AC, AJ.

The formula in H3 should actually be =INDEX(PrjctForecast!3:3,,COLUMNS($H3:H3)*11/7-11/7+17) but that wouldn't cause the problem that you are seeing.

 

[tomehta]

he formula in H3 should actually be =INDEX(PrjctForecast!3:3,,COLUMNS($H3:H3)*11/7-11/7+17) but that wouldn't cause the problem that you are seeing.

That was indeed the issue, I was referring to the PRjctForecast sheet columns rather than the parent sheets columns.

 

[tomehta]

thanks once again !

 

[jasonb75]

Ideally the COLUMNS() range should refer to the cell holding the first formula.

Having it refer to something else would not make a difference unless you delete a column (as in right click and delete, not just use the delete key to empty the cells).