Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

put on hold as unclear what you're asking by Excellll, fixer1234, DavidPostill, mdpc, Nifle yesterday

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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

2 Answers 2

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:

enter image description here

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.

share|improve this answer

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 PivotTables can only deal with numeric values (!!!), so you'd have to convert your x,y,z,p into something numeric first.

share|improve this answer
pivot tables can deal with text –  Raystafarian Jul 29 '13 at 17:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.