Extract part of matrix

Dear all,

I have a question on how to extract a part of a matrix. The following is the case: I have a square matrix and need to extract multiple rows and columns of that matrix. For instance, I have a 5 x 5 matrix and need to extract the 2nd and 4th rows and columns to create another 2 x 2 matrix (as depicted in the figure below). What excel function should i use to obtain this result?

matrix.png

Thanks in advance!

Michiel

One possibility, using OFFSET:

=OFFSET($C$5,1+2*(ROWS($1:1)-1),1+2*(COLUMNS($A:A)-1),,)

A better (non-volatile) alternative:

=INDEX($C$5:$G$9,2*ROWS($1:1),2*COLUMNS($A:A))

I'm not sure if I follow your request? run that past me again... only more slowly!! Think 10-year old.

You mean something like 2nd and 4th element from vector?

Either for horizontal:

Formula:

Please Login or Register  to view this content.

or for vertical

Formula:

Please Login or Register  to view this content.

Spot on for an explanation for a 10-year old... and me.

So, for a more complicated matrix, please post a sample sheet.

One last guess....

=INDEX($C$5:$G$9,CHOOSE(ROWS($1:1),$B$16,$B$17),CHOOSE(COLUMNS($A:A),$B$16,$B$17))

I am HOPING that the shading in your source data is irrelevant. I can't see any logical connection between it and "input".

So, one Named range (Input):

=Sheet1!$B$24:INDEX(Sheet1!$B$24:$B$50,COUNTA(Sheet1!$B$24:$B$50))

and one formula copied across and dnown:
=IFERROR(INDEX($B$2:$S$19,AGGREGATE(15,6,Input,ROWS($1:1)),AGGREGATE(15,6,Input,COLUMNS($A:A))),"")

see sheet.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.