Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it any easy way to create a "pivot"ed table of data to compare result data in several columns, but without actually summing/counting or doing other pivot operations?


arrange the following

A  B  C  D  E
1  10  101 201 X
1  20  102 202 Y
2  10  103 203 Z
2  20  104 204 Q

like so
A  C'  C'' D' D'' E' E''
1  101 102 201 202 X Y
2  103 104 203 204 Z Q

where the value of B determines which column to show the data from C or D in.

(hoping the example will make the question possible to understand)

I can think of doing lookups with concatenated values of A and B, or creating the data in several steps, but it would be rather cumbersome. Are there any shortcuts to get this functionality in Excel?

share|improve this question

This gets you pretty close. In Excel 2007, it uses the "classic Pivot Table Layout" in the Display tab of the "Pivot Table Options" dialog.

enter image description here

share|improve this answer
Hi, I was thinking of doing it like that, using sum (which will work because usually there will be only one value in each field and no real aggregates). However we will run into problems if there are e.g. text values in the data (not present in my example, sorry) – grojo Nov 2 '11 at 9:12
Ah, then, you're right --- vlookups using concatenations of A and B is the fastest. – F106dart Nov 16 '11 at 14:41
Digging around on the web I found this link very pertinent to your problem: In particular, the array formula using INDEX+MATCH – F106dart Nov 16 '11 at 21:01

Here's a solution with array formulas using "INDEX-MATCH". Remember that array formulas have to be entered with CTRL+SHIFT+ENTER, which will put curly brackets {} around the formula.

(Inspiration for this approach came from )

See the screenshot below.

enter image description here

After putting your data in cells A1:E5, I created 5 named ranges for each column. Then I set up the results area in your desired layout. Cells B18:G19 have "INDEX-MATCH" array formulas as shown in the 2nd screenshot. (Only the first 3 columns would fit in the screenshot; sorry!) As an example, here's the formula from cell B18:

{=INDEX(C_list,MATCH($A18 & B$17,A_list & B_list,0))}

enter image description here

And, after all that, I still think it's easier just to concatenate A & B and then vlookup!

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .