|Thank you very much for the kind words.|
I truly was not fishing for compliments, but it was much appreciated in any case.
I'm quite serious about my curiosity as to the thought process (or pure luck?) that allows seemingly unrelated functions to work so well together.
re:" is way over my head and suspect most others too."
Although it may seem so, in reality it's not all that complicated. It's actually very logical.
The array in this case is really just what some people call a table. e.g. 9 values in a range, say A1:C3. 3 rows by 3 columns.
The INDEX function can pull a number from this array if you give it a row/column intersection.
INDEX(A1:C3,1,1) returns the value at the intersection of Row 1/Column 1 of the array. In this case it's A1, but if the array was D2:H5, the intersection of Row 1/Column 1 would be D2.
The point of this thread was that Excel allows you to use other functions to "build" the arguments that the functions use.
e.g. Instead of using "1" as the row_num argument, I can use SUM(2,-1) which evaluates to 1, or I could use VLOOKUP to grab a row number for another array/table.
In the case of the formula I posted above, I use the ROW() and COLUMN() functions to figure out what cell the formula is in and then adjust that value via arithmetic operations and the INT function to get the actual row/column intersection I'm looking for.
It's that combination of functions that I find so intriguing.
I doubt the developers foresaw the need to transpose a 3 x 3 array into a single column, yet they provided the exact set of functions, when arranged properly, that do just that.
Was that planned functionality or divine intervention? ;-)