VLOOKUP need to increment col_index_num
 

If I have =VLOOKUP(date,data,2) and in the next cell down I want
=VLOOKUP(date,data,5) so the col_index_num increments by 3 each time how
would I go about doing this so that it can be copied down instead of editing
each line by hand?

Many Thanks in advance

VLOOKUP need to increment col_index_num
 

You could incorporate the row function. If, for example, this began in row
2, instead of the 2, enter =3*(row()-2)+2. In row 2 that evaluates to 2.
But copy it down to row 3 and it evaluates to 5, etc.

"Kia" wrote:

If I have =VLOOKUP(date,data,2) and in the next cell down I want
=VLOOKUP(date,data,5) so the col_index_num increments by 3 each time how
would I go about doing this so that it can be copied down instead of editing
each line by hand?

Many Thanks in advance

VLOOKUP need to increment col_index_num
 

It would be more robust to allow the formula to be started (entered) in
*any* row (cell), and still allow the column index to increment by 3 as it's
copied down:

=VLOOKUP(Date,Data,3*ROWS($1:2)-4)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"bpeltzer" wrote in message
...
You could incorporate the row function. If, for example, this began in
row
2, instead of the 2, enter =3*(row()-2)+2. In row 2 that evaluates to 2.
But copy it down to row 3 and it evaluates to 5, etc.

"Kia" wrote:

If I have =VLOOKUP(date,data,2) and in the next cell down I want
=VLOOKUP(date,data,5) so the col_index_num increments by 3 each time how
would I go about doing this so that it can be copied down instead of
editing
each line by hand?

Many Thanks in advance

VLOOKUP need to increment col_index_num
 

Thank you both so much
bpeltzer
your's worked but I can not guarantee the formula will begin in row 2 on
summary

RagDyer
I had to edit your a little to this

=VLOOKUP(date,data,3*ROWS($1:2)-3)

but it does the job I require

Many thanks to you both

VLOOKUP need to increment col_index_num
 

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Kia" wrote in message
...
Thank you both so much
bpeltzer
your's worked but I can not guarantee the formula will begin in row 2 on
summary

RagDyer
I had to edit your a little to this

=VLOOKUP(date,data,3*ROWS($1:2)-3)

but it does the job I require

Many thanks to you both

VLOOKUP need to increment col_index_num
 

Try this, works anywhere on the spreadsheet:
=VLOOKUP($A2,'Sheet2'!$A$2:$IV$65536,COLUMNS($A2:B 2),FALSE)

or this (slightly shorter, same results)

=VLOOKUP($A2,'Sheet2'!$2:$65536,COLUMNS($A2:B2),FA LSE)



"Kia" wrote:

If I have =VLOOKUP(date,data,2) and in the next cell down I want
=VLOOKUP(date,data,5) so the col_index_num increments by 3 each time how
would I go about doing this so that it can be copied down instead of editing
each line by hand?

Many Thanks in advance

VLOOKUP need to increment col_index_num
 

or this (slightly shorter, same results)
=VLOOKUP($A2,'Sheet2'!$2:$65536,COLUMNS($A2:B2),F ALSE)

Even shorter:

=VLOOKUP($A2,Sheet2!$2:$65536,COLUMNS($A:B),0)


--
Biff
Microsoft Excel MVP


"Cyno" wrote in message
...
Try this, works anywhere on the spreadsheet:
=VLOOKUP($A2,'Sheet2'!$A$2:$IV$65536,COLUMNS($A2:B 2),FALSE)

or this (slightly shorter, same results)

=VLOOKUP($A2,'Sheet2'!$2:$65536,COLUMNS($A2:B2),FA LSE)



"Kia" wrote:

If I have =VLOOKUP(date,data,2) and in the next cell down I want
=VLOOKUP(date,data,5) so the col_index_num increments by 3 each time how
would I go about doing this so that it can be copied down instead of
editing
each line by hand?

Many Thanks in advance