Complicated Lookup Function - Pulling Info From One Sheet To Another

Maybe this isn't as complicated as it seems to be in my head, but essentially I'm trying to pull inventory numbers from one spreadsheet into another. However, the numbers I need to pull in Spreadsheet 1 are listed vertically and I'd like that info to be listed horizontally on Spreadsheet 2.

Here is a quick reference of what I'm trying to achieve. I was able to pull the product names to my second sheet using a VLookup function (woo-hoo!) but trying to pull the inventory numbers by location to Sheet 2 is proving... difficult. Very difficult for me, anyway, especially given how large the spreadsheet is and how the info is formatted. Additionally, some products have additional locations beyond what is listed below - for example a few randomly have a location called "EVENTS", so the Shop Names aren't necessarily consistently placed within the table. So it really needs to be a function that looks up the Qty next to the Shop Name listed under the Item #? Is that even possible?

I've color coded what info I would like to pull and where I need it placed (lighter shades = Info I need pulled, darker shades = shop locations I'm referencing). I hope that makes sense!

SHEET 1

SHEET 2