Can I increment a lookup's row value by 4?

stuckagain22

Board Regular
Joined
Aug 4, 2006
Messages
182
Here is my current code:

Code:
VLOOKUP(Creation!$B87,data!$E$218:$G$242,3,0)

Four rows down I have:

Code:
VLOOKUP(Creation!$B88,data!$E$218:$G$242,3,0)

I need to copy and paste this formula through many rows and I was wondering if there is a way I can increment the value in red by 1, for every 4th row?

(Note the rows between these two rows of code are blank.)

Any help would be appreciated.

EDIT: Removed Color tags, they don't work within the Code tags - Moderator
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

=VLOOKUP(INDEX(Creation!$B$87:$B$107,ROWS($A$2:$A2)*4-4+1),data!$E$218:$G$248,3,0)

adjust the bolded range.

HTH
 

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
Using Creation!$B87 as the initial reference point, using the following offset function should work for you:

=offset(Creation!$B$87,row()/4-1,0)

in the vlookup formula, it now looks like this

VLOOKUP(offset(Creation!$B$87,row()/4-1,0)
,data!$E$218:$G$242,3,0)

The catch though is the row() component. If you start using this formula in row 4 it will work fine, however if you start in, say, row 10, you will need to add -6 to the formula, i.e. row()-6/4

Basically the first formula needs to offset to zero, the next formula (4 rows down) will offset by 1 and so on.

Good luck
 

Forum statistics

Threads
1,136,260
Messages
5,674,700
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top