Finding values based on different rows and columns

Question

Starting row 3, I have 'specific values' repeating on

every 3rd 7th 11th 15th...up to 43rd column
then it goes down to 19th row (16 rows down) and repeats it to above columns then it goes down to 16 rows below that and repeats (note this point)

I want to pull down these specific values on a column starting from row 2 (eg: from B2 downwards)

For the first part, I was able to get this

=OFFSET($C$3,0,4*(ROW()-1))

This works perfectly for the row. It pulls every 3rd 7th 11th 15th columns...

I can even put a 'if formula' to see if the column is 43 then tell it to go down 19 rows (by putting an indirect formula inside offset).

However, this has to be repeated as well (as noted above). How can I go on to do that?

Thanks for your time!

So the row part would be something like Mod(row()-1,11) You may need to adjust the math a little. Then you will need to reset the column part so you will need to make the column something like 4*(ROW()-1)-(11*(Mod(row()-1,11))) again I did not test it but this should get you close. =OFFSET($C$3,Mod(row()-1,11),4*(ROW()-1)-(11*(Mod(row()-1,11)))) — Scott Craner
– Scott Craner, Commented Dec 22, 2015 at 16:33
I am little confused. 1. The formula didn't work (but I am working on it) 2. Why 11? the row repeats every 16th times starting from column 3. Column repeats every 4th time starting from column 3 though Also on the side note, would Vlookup + Hlookup work better? Just thinking — Dingo
– Dingo, Commented Dec 22, 2015 at 16:54
Sorry I meant quotient not mod =OFFSET($C$3,QUOTIENT(ROW()-1,11),(4*(ROW()-1-(11*(QUOTIENT(ROW()-1,11)))))). It is 11 because that is the number of columns. It needs to reset to offset of 0 on the column every 11 rows of the formula column. — Scott Craner
– Scott Craner, Commented Dec 22, 2015 at 17:09

Scott Craner · Accepted Answer · 2015-12-22 18:07:51Z

1

This one has been tested. Put this in B2 and copy down:

=OFFSET($C$3,QUOTIENT(ROW()-2,11)*16,(4*(ROW()-2-(11*(QUOTIENT(ROW()-2,11))))))

Edit Sorry missed the part that it needs to skip every 16 rows. Fixed that.

answered Dec 22, 2015 at 17:21

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

@Shanan Sorry missed the part that it needs to skip every 16 rows. Fixed that. See Edit.

@Shanan always glad to help.