Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

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
Asked:
dabug80
1 Solution
 
Subodh Tiwari (Neeraj)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))

Open in new window

0
 
Naresh PatelTraderCommented:
post sample file
0
 
Rgonzo1971Commented:
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))

Open in new window

Regards
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Lol
0
 
dabug80Author Commented:
Great. Worked well.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks for the feedback.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now