I just reread the thread and realized that using Index for dynamic formulas has already been discussed.

So, perhaps I should stress this point from my last post:
Quote Originally Posted by Whizbang
Also, using named ranges will allow Excel to calculate the size of the range only once, rather than every for every cell that uses your sumproduct formula.
Named ranges are great. They allow you to use a formula to calculate a value, and then use that same value or reference throughout the workbook. As I stated above, using named ranges prevents Excel from calculating the value or reference for every iteration of a formula. If you have copied a formula down a column, and that formula has duplicate calculations, then you need to pull those calculations out and into either a single cell or into a named range. Doing so will allow Excel to do that calculation only once, and speed up your workbook considerably.