Solved

Excel: Index/Match change range on fill down

Posted on 2016-07-18
6
67 Views
Last Modified: 2016-07-18
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
Comment
Question by:dabug80
6 Comments
 
LVL 30

Accepted Solution

by:
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))

Open in new window

0
 
LVL 8

Expert Comment

by:itjockey
ID: 41716339
post sample file
0
 
LVL 50

Expert Comment

by:Rgonzo1971
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))

Open in new window

Regards
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41716358
Lol
0
 
LVL 1

Author Closing Comment

by:dabug80
ID: 41717994
Great. Worked well.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41718130
Thanks for the feedback.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question