Index Match Match or magic?

[eggyacid]

I'm pulling my hair out trying to build this spreadsheet. Can't figure out how to attach so I made a link to my sample sheet

https://1drv.ms/x/s!AqBmRZQb295xyn0FLyeCYZesi8YS

an example...
I get a weekly data dump in excel and I can copy and paste that into a sheet in excel, then on the main page I need to pull data from the data dump.

I created a sample excel, hopefully someone can help before I go bald.

Drop down list of 3 names, 3 location, 3 weeks and needs result return that matched.

 

[eggyacid]

 

[AhoyNC]

This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1
2	Name	Location	Week	Volume
3	Amy	CA	W1	230
Month

Excel Workbook
	A	B	C	D	E
1	Name	Location	W1	W2	W3
2	Joe	NY	100	120	90
3	Joe	TX	150	200	300
4	Joe	CA	130	230	100
5	Amy	NY	120	90	100
6	Amy	TX	200	300	150
7	Amy	CA	230	100	130
8	Susan	NY	230	130	100
9	Susan	TX	90	120	150
10	Suan	CA	300	200	130
Week