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

stuckagain22

Board Regular
Joined
Aug 4, 2006
Messages
183
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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,171,241
Messages
5,874,613
Members
433,060
Latest member
studyiqedu

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