# How can I create matrices of data in Excel?

I want to create a 4x4 matrix in Excel 2007 by taking three or more columns or conditions, for example:

``````Column index  Row index     Name
1             2             x
2             3             y
3             4             z
4             1             p
``````

This is how data looks, and I want it for 1x1 cell as p and 1x2 cell as x and so on, and I want output as follows:

``````matrix  1   2   3   4
1       p   x   y   z
2       p   x   y   z
3       p   x   y   z
4       p   x   y   z
``````

And I have very huge data like this. Sometimes the matrix size goes up to 60x60.

-
Not sure I understand what you are asking. It seems the numbers in the "Column index" column are irrelevant. You basically create a row with x,y,z,p ordered by their "Row Index", then copy that row 4 times, is that it? –  assylias May 31 '12 at 14:34
I have to agree with @assylias. Your depicted output doesn't match what you describe as "...for 1*1 cell as p and 1*2 cell as x...". But please see my answer below. –  F106dart May 31 '12 at 17:25
Hello assylias because it is a small data thats y it is appearing like that we represent a matrix in terms of 1*1, 1*2, 1*3, 1*4, and 2*1, 2*2 w.r.to column index. and I want to fill the same matrix w.r.to row index also for that reason only I have mentioned the out put matrix in that fashion. when we see 1*2 and 2*1 both the values are same I want to write all in a single matrix. some times the matrix size reaches to 60*60 also please give me a better solution for this –  sandeep Jun 1 '12 at 7:32

I think you are wanting a multi-condition lookup. Here is a great example

Following the techniques in that link, you can do something like this:

Set up 3 named ranges covering your data. Note that the formula in J9 is an array formula, entered with CONTROL+SHIFT+ENTER. The nice thing about that formula is once you enter it as an array formula for just one cell, you can then drag it over and down as far as you need.

Also: the `IFERROR()` part puts in blanks (instead of #N/A or #VALUE errors) if an error is encountered in the `INDEX(MATCH()` part.

-

You need to use a `PivotTable`. This is available from Insert > PrivotTable. Then you can put the `RowInd` in the Row Labels and `ColInd` in the Column Labels, and your Name into the Values box. However, it appears that `PivotTable`s can only deal with numeric values (!!!), so you'd have to convert your `x,y,z,p` into something numeric first.

-
pivot tables can deal with text –  Raystafarian Jul 29 '13 at 17:04