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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top