Lookup to get data from one column into multiple

[ashley1984]

I'm trying to pull some data from one range into another. The data range where the data is stored is arranged in the first table

I want to pull this data into a table that looks like the second table with the channel in a separate column for each channel.

I could simply do a past special and transpose, but I update the file weekly and want the file to update each week automatically.

I've tried an Index Match but have not had any luck.

Any ideas?

Thanks

 

[GraH]

Maybe you can use the dynamic array functions that come with 365.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		TPN	Item Name	Channel	Sales				Bi Store	Internet	Medium Store	Small Store
3		88245336	Product 1	Internet	5,5			Product 1	10	5,5	8,5	6
4		88245336	Product 1	Bi Store	10			Product 2	12	6	8	5
5		88245336	Product 1	Medium Store	8,5
6		88245336	Product 1	Small Store	6
7		88245964	Product 2	Internet	6
8		88245964	Product 2	Bi Store	12
9		88245964	Product 2	Medium Store	8
10		88245964	Product 2	Small Store	5
11
12
13
14
Sheet1

Cell Formulas
Range	Formula
I2:L2	I2	=TRANSPOSE(SORT(UNIQUE(Sales[Channel])))
H3:H4	H3	=SORT(UNIQUE(Sales[Item Name]))
I3:L4	I3	=SUMIFS(Sales[Sales],Sales[Item Name],H3#,Sales[Channel],I2#)
Dynamic array formulas.