Index/Match Problem

[Graham]

I have an array laid out similar to that below with 22 columns and 20 rows,
say A1::V20. There are column headings Class 1, Class 2, Class 3 etc.

Class 1 Class 2 Class 3 etc
one two three
six eight four
nine three eight
etc
In another sheet I have a cell B5, which has data validation drop down list
with all the Column headings above. When I select a column heading in this
cell need the column list to appear in the rows below, as per the array
above. eg if Class 2 is selected then the cell below would have two, the
next row eight, and the next three. I cannot seem to get a combination of
index and match , which I think is the right route to give me the correct
values in relation to the column heading. I would as always value any
guidance.

Kind Regards,
Graham Haughs
Turriff
Scotland.

 

[Ron Rosenfeld]

I have an array laid out similar to that below with 22 columns and 20 rows,
say A1::V20. There are column headings Class 1, Class 2, Class 3 etc.

Class 1 Class 2 Class 3 etc
one two three
six eight four
nine three eight
etc
In another sheet I have a cell B5, which has data validation drop down list
with all the Column headings above. When I select a column heading in this
cell need the column list to appear in the rows below, as per the array
above. eg if Class 2 is selected then the cell below would have two, the
next row eight, and the next three. I cannot seem to get a combination of
index and match , which I think is the right route to give me the correct
values in relation to the column heading. I would as always value any
guidance.

Kind Regards,
Graham Haughs
Turriff
Scotland.

Perhaps something like this:

=INDEX(Sheet1!$A$1:$V$20,ROW()-4,MATCH($B$5,Sheet1!$A$1:$V$1,0)) in B6 and
copy/drag down as far as needed.

Blanks in the original array will give 0's with this formula, so that may have
to be tested for if an issue.


--ron

 

[Graham]

Many thanks Ron, that worked a treat.

Graham

Perhaps something like this:

=INDEX(Sheet1!$A$1:$V$20,ROW()-4,MATCH($B$5,Sheet1!$A$1:$V$1,0)) in B6 and
copy/drag down as far as needed.

Blanks in the original array will give 0's with this formula, so that may have
to be tested for if an issue.


--ron

 

[Ron Rosenfeld]

Many thanks Ron, that worked a treat.

Graham

You're welcome. Thanks for the feedback.


--ron