INDEX/MATCH and RANK formula array querys

Hello everyone!

Many thanks again for all the help provided both directly and in terms of reading the posts of others. Haven't found an answer to these yet tho.

I have 8000 lines of data, divided across 7 worksheets split by subtotals and totals. Some of these lines have a figure that I need to pull through to another worksheet (this may change month on month).

1. To generate a reference for this line to match and index it to, I've used the rank formula but wondered if it's possible to a) use an array across the various sheets (so far it returns #VALUE) and a way to avoid including the subtotals and totals.

2. I need to use index and match (I think) across the 7 worksheets (only two attached in example) to pull through matching data in the summary. Again not sure if this is possible, if tried but I'm getting no where fast and I'm ashamedly very new to index / match functions.

I've attached an example in the hope this makes it a little clearer.

Really grateful for any help!