Help with a lookup

[ajnmx]

Ok, this is driving me mad. I feel I should know the answer but I've
tried various combinations of lookups, indirects, offsets etc and I
just can't figure it out!

I have a list of data:


AAA
Jan 15
Feb 20
Mar 25

BBB
Jan 100
Feb 120
Mar 130

....and this goes on somewhat. I want to set up a new table like so:

Jan Feb Mar
AAA
BBB

....and populate it with the data from the first table. I can of course
do it with lookups but I have to change the range for each line. I
want to use one formula that I can just copy to all cells and it will
give the appropriate value. How???

 

[Max]

One try ..

Assume source data in Sheet1, cols A and B, from row1 down

In Sheet2,

you have this set-up:

Jan Feb Mar
AAA
BBB

where the AAA, BBB, etc are listed in A2 down

Put in B2:
=OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)+COLUMNS($A:A)-1,1)
Copy B2 across and fill down to populate

---

 

[Herbert Seidenberg]

Or try Pivot Table.
Move AAA and BBB above your numbers, like this:
AAA
Jan 15
Feb 20
Mar 25

BBB
Jan 100
Feb 120
Mar 130

Data > Pivot Table > Multiple consolidation ranges
Ranges: Enter A1:B4 , A6:B6 and so on
Layout: Drag Row into COLUMN and Column into ROW
DATA is Sum of Value
Options: Uncheck Grand Totals and AutoFormat
The result will look like this:

Column Jan Feb Mar
AAA 15 20 25
BBB 100 120 130