Solved

# Excel: Index/Match change range on fill down

Posted on 2016-07-18
54 Views
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
Question by:dabug80

LVL 28

Accepted Solution

Subodh Tiwari (Neeraj) earned 500 total points
ID: 41716337
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

LVL 8

Expert Comment

ID: 41716339
post sample file
0

LVL 48

Expert Comment

ID: 41716342
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

LVL 28

Expert Comment

ID: 41716358
Lol
0

LVL 1

Author Closing Comment

ID: 41717994
Great. Worked well.
0

LVL 28

Expert Comment

ID: 41718130
Thanks for the feedback.
0

## Featured Post

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity anâ€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.