Increment number by X within formula

[cidfidou]

Hi Excel Gurus,

i would like to come up with a formula that will increment a number in order to place it whithin a vlookup.

I think the rows formula is the answer but i cant figure it out?

I would like to have a formula giving me the following in the third argument of my vlookup in order to paste the vlookup down a massive excel Spreadsheet
3
7
11
15
19

 

[Jonmo1]

Try

=VLOOKUP(val,range,(COLUMNS($A1:A1)-1)*4+3,FALSE)

DO NOT change the $A1:A1, it has no relation to your data.
The *4 represents the inteveral (every 4th)
The +3 represents the first # to use (3)

 

[JackDanIce]

n/a

 

[cidfidou]

Hi jommo,

thank you so much for your prompt answer. I need to paste the formula down my spreadsheet so i think the rows formula should be used ( =ROWS((A$1:A1)-1)*4+3)

Sry for being slow, but I can t figure out how to trick your formula to start it with 3 and to have then 7,11,15,19.....

Thanks in advance

 

[Jonmo1]

You missplaced a Paren

=VLOOKUP(val,range,ROWS((A$1:A1)-1)*4+3,FALSE)
Should be
=VLOOKUP(val,range,(ROWS(A$1:A1)-1)*4+3,FALSE)


You won't actually see the numbers incriment 'within the formula'
But the reference will change accordingly.

 

[cidfidou]

Hi Jommo,

I got it. Thanks a million

=ROWS((A$1:A1)-1)*4-1

 

[Jonmo1]

No that's not right (although it probably works, but it's better as above)
refresh your browser, I had a typo.

 

[cidfidou]

Thanks Jommo. I will be using yours.. I am always impressed with people like you ready to help strangers. have a nice day

 

[Jonmo1]

Glad to help, thanks for the feedback.