Series fill a formula down a column automatically skipping a set number of rows

I am trying to fill a formula down a column every 6th row but incrementing the variable in the formula ($A2) for each entry. The formula uses the Take command and inputs 5 rows of data, so to keep it...

community

excel

Formulas and Functions

Macros and VBA

Office Scripts

SteveK14

Occasional Reader

Jun 04, 2026

My apologies, I posted before I added the formula and had to wait for it to be approved before I could reply and add it. Thank you very much for responding. I'm not familiar with some of those commands you list, but if they accomplish this, great! Basically, I need the formula to increment the variable $A2, like a flash fill, in this formula, but every 6 rows.

=TAKE(DROP('Active EE HRODS 2026-05-21'!$A:$W, MATCH('DO Count'!$A2,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0),0),5)

I'm matching a building code on the 'DO Count' sheet with a list of employees assigned to that building on the 'Active EE HRODS' sheet and listing the top 5 rows for that building on a separate sheet.

m_tarler

Silver Contributor

Jun 04, 2026

here are a couple of options for you to try:

the REDUCE-LAMBDA version:

=IFERROR(REDUCE({"Header","row","of","data"},
                  MATCH('DO Count'!$A2:A999,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0),
                  LAMBDA(p,q,VSTACK(p,
                                            CHOOSEROWS('Active EE HRODS 2026-05-21'!$A:$W,q+SEQUENCE(5,,0))
                                             ,""))
   ),"")

in this case that {"Header",....} part is a 'throw away' row that can be used to create the header row of the data. You can Probably replace that with 'Active EE HRODS 2026-05-21'!$A1:$W1 or you can wrap the whole function with a DROP(... ,1)

The MATCH I copied from you (but would personally update it to XMATCH) with 1 difference being I used the entire range to be searched $A2:$A999 (or what ever range you need) or use a . after the colon to auto trim the range like $A2:.$A999

Inside the LAMBDA it is stacking the found row + 4 more with a blank row to each prior found rows. NOTE I used CHOOSEROWS with a SEQUENCE of 0,1,2,3,4 instead of TAKE(DROP(...)) but either would work.

another option avoiding the LAMBDA functions is

=IFERROR(CHOOSEROWS( VSTACK( "", 'Active EE HRODS 2026-05-21'!$A:$W),
                                            TOCOL( EXPAND( MATCH('DO Count'!$A2:.$A999,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0)+SEQUENCE(,5),,6,1))),"")

in this case I add a row before all the data that is 'blank' (i.e. the VSTACK in line 1)

then I use CHOOSEROWS to select all the 'MATCH' rows and add 1,2,3,4,5 (similar to above but because of the added row I start at 1 instead of 0) in this case creating a grid of the desired row values. I use EXPAND to add another column that are all the value 1 (i.e. the blank row I added in line 1) then use TOCOL to make it a single list of values.

I did test this on a test sheet but because my test sheet didn't use the same names and ranges as you I hope my translation of those values are correct. At the very least it gives some creative ideas on how to solve your problem without VBA

Forum Discussion

Series fill a formula down a column automatically skipping a set number of rows