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

#### stuckagain22

##### Board Regular
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
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
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

#### stuckagain22

##### Board Regular
Thanks Krish and Johnny, that will save me a lot of time!

Replies
3
Views
109
Replies
3
Views
318
Replies
8
Views
183
Replies
14
Views
473
Replies
12
Views
635

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?

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