Need formula to consolidate data

[emmac]

The top table is my sample data and the bottom table is my desired outcome.

I can't figure out a formula to pull the VolumeUsed data for 1 location ID across the associated columns for the item numbers.
I tried index, match, and could only get the first item number for that location to populate.
To pull this data currently, I have had to make separate sheets for each item number and then VLOOKUP, but when I have 9 item numbers and 150 locations I need to summarize, making individual sheets for each item takes too much time.

Am I misunderstanding indexing / matching?

Sample data:

Location ID #	Item Number	VolumeUsed	AmountUsed	Week Ending
1	1234​	0.5​	45​	5/5/2024​
1	1235​	1​	200​	5/5/2024​
1	1236​	1.5​	37.5​	5/5/2024​
1	1237​	0.25​	15​	5/5/2024​
2	1234​	0.25​	22.5​	5/5/2024​
2	1235​	1.25​	250​	5/5/2024​
2	1236​	0.5​	12.5​	5/5/2024​
2	1237​	0.4​	24​	5/5/2024​
3	1234​	2​	180​	5/5/2024​
3	1235​	4​	800​	5/5/2024​
3	1236​	1.25​	31.25​	5/5/2024​
3	1237​	1.5​	90​	5/5/2024​
4	1234​	2​	180​	5/5/2024​
4	1235​	3.5​	700​	5/5/2024​
4	1236​	1.75​	43.75​	5/5/2024​
4	1237​	2​	120​	5/5/2024​
5	1234​	0.25​	22.5​	5/5/2024​
5	1235​	0.75​	150​	5/5/2024​
5	1236​	1​	25​	5/5/2024​
5	1237​	0.2​	12​	5/5/2024​

Desired outcome:

	Volume Used Week Ending 5/5/24
	Item Number
Location ID #	1234​	1235​	1236​	1237​
1	0.5​	1​	1.5​	0.25​
2	0.25​	1.25​	0.5​	0.4​
3	2​	4​	1.25​	1.5​
4	2​	3.5​	1.75​	2​
5	0.25​	0.75​	1​	0.2​

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Location ID #	Item Number	VolumeUsed	AmountUsed	Week Ending			1234	1235	1236	1237
2	1	1234	0.5	45	05/05/2024		1	0.5	1	1.5	0.25
3	1	1235	1	200	05/05/2024		2	0.25	1.25	0.5	0.4
4	1	1236	1.5	37.5	05/05/2024		3	2	4	1.25	1.5
5	1	1237	0.25	15	05/05/2024		4	2	3.5	1.75	2
6	2	1234	0.25	22.5	05/05/2024		5	0.25	0.75	1	0.2
7	2	1235	1.25	250	05/05/2024
8	2	1236	0.5	12.5	05/05/2024
9	2	1237	0.4	24	05/05/2024
10	3	1234	2	180	05/05/2024
11	3	1235	4	800	05/05/2024
12	3	1236	1.25	31.25	05/05/2024
13	3	1237	1.5	90	05/05/2024
14	4	1234	2	180	05/05/2024
15	4	1235	3.5	700	05/05/2024
16	4	1236	1.75	43.75	05/05/2024
17	4	1237	2	120	05/05/2024
18	5	1234	0.25	22.5	05/05/2024
19	5	1235	0.75	150	05/05/2024
20	5	1236	1	25	05/05/2024
21	5	1237	0.2	12	05/05/2024
Sheet6

Cell Formulas
Range	Formula
H1:K1	H1	=TOROW(UNIQUE(B2:B21))
G2:G6	G2	=UNIQUE(A2:A21)
H2:K6	H2	=SUMIFS(C:C,A:A,G2#,B:B,H1#)
Dynamic array formulas.