Dragging a formula horizontally whilst searching vertically

Hello,

I have a large document with lots of separate sheets for different retailers with one main summary sheet.

On the main 'summary' sheet, I want to pull back information from each of the retailer sheets. The summary sheet is working horizontally, while the retailer sheets are working vertically.
This means I want to pull a formula across, whilst returning search result from a separate sheet going vertically

I have used Index formulas however because the cells in the sheet I am looking to are merged, it isn't pulling back the correct merged cell reference and instead is looking at each individual cell and isnt working

I think I could use a sumif formula to do this, but because the cells are pulling back text, I cant seem to do this

If someone could help I would be very grateful!