Dynamic Range and Index Match

I have written these questions on the attached spreadsheet

Question 1 How do I write the correct formula for the above row 4
Question 2 How can I make the named ranges dependent on the values in column A
so if I load new data into columns B and C (the new data may extend down additional rows)
With the new data - RANGE1 would expand addl rows if "BRONTON" was in more rows that is
curtenly shown - and so-on for the other ranges.

Thanks Carroll