VLOOKUP need to increment col_index_num

[Guest]

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

 

[Guest]

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.

 

[RagDyer]

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

 

[Guest]

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

 

[RagDyer]

Thanks for the feed-back.

 

[Cyno]

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

or this (slightly shorter, same results)

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

 

[T. Valko]

or this (slightly shorter, same results)

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

Even shorter:

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