# Pivoting list without totals / aggregates

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?

example:

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?

-

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.

-
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: chandoo.org/wp/2010/11/02/multi-condition-lookup 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 chandoo.org/wp/2010/11/02/multi-condition-lookup )

See the screenshot below.

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))}

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

-