I am crushing my spreadsheet with maxif arrays to the point where it's running out of memory because i'm referring to entire columns. My data set changes constantly so I can't hard code the number of rows I have in my sheet.

I have been searching the forum trying to understand how fix this so that I only use the number of rows in my dataset.

I am trying to grasp the concept of naming a data range dynamically. I'm referring to the "pulling out minimum and maximum values..!" thread from this morning. I've downloaded the excel example and trying to understand the calls to the range as well as the formation of the named range.

Name: _Data

Refers to: =aaaa!$A$2:INDEX(aaaa!$C:$C,MATCH(9.99E+307,aaaa!$C:$C))

In this example, can you explain what the INDEX MATCH are doing to name this range dynamically?

Thanks!