• Status: Solved
• Priority: Medium
• Security: Public
• Views: 147

Excel: Index/Match change range on fill down

Hello,

I have the following formula:
=INDEX('Data'!\$G4:\$AE4,MATCH(Sheet2!I\$3,'Data'!\$G3:\$AE3,0))

I would like to fill this formula down but have both the ranges:
'Data'!\$G4:\$AE4
'Data'!\$G3:\$AE3

E.g

=INDEX('Data'!\$G4:\$AE4,MATCH(Sheet2!I\$3,'Data'!\$G3:\$AE3,0))
=INDEX('Data'!\$G7:\$AE7,MATCH(Sheet2!I\$3,'Data'!\$G6:\$AE6,0))
=INDEX('Data'!\$G10:\$AE10,MATCH(Sheet2!I\$3,'Data'!\$G9:\$AE9,0))

increase in increments of 3 as I fill down. Is this possible?
0
dabug80
1 Solution

Excel & VBA ExpertCommented:
Try something like this......
``````=INDEX(OFFSET(Data!\$G\$4:\$AE\$4,(ROWS(A\$1:A1)-1)*3,),MATCH(Sheet2!I\$3,OFFSET(Data!\$G\$3:\$AE\$3,(ROWS(A1:A\$1)-1)*3,),0))
``````
0

post sample file
0

Commented:
HI,

pls try ( adjust H2 and H\$2)

``````=INDEX(OFFSET(Data!\$G\$4:\$AE\$4,3*(ROW(H2)-ROW(H\$2)),0),MATCH(Sheet2!I\$3,OFFSET(Data!\$G\$3:\$AE\$3,3*(ROW(H2)-ROW(H\$2)),0),0))
``````
Regards
0

Excel & VBA ExpertCommented:
Lol
0

Author Commented:
Great. Worked well.
0

Excel & VBA ExpertCommented:
Thanks for the feedback.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.