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

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

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

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

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

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

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