Array elements from MMULT

Post some sample data and tell us what result you expect.

First question: with 1E6 by 16k cells, why are you worried about using too many cells? IMO, the easiest first approach is to do like you are doing -- compute the resulting product matrix, then find the largest value (probably a two step process of finding the largest in each row or column, then find the largest in that resulting vector), then return the desired row and column for that value (using MATCH() functions).

Perhaps it is my emphasis on understanding the math behind a problem, but it seems like it should be possible to locate the max value in the result matrix without computing the entire matrix in this case. Can I assume that you can recall the definitions of matrix multiplication well enough to "work backwards" from the max in the result matrix? Here's the thought process:

1) Looking at the matrix product of vector1*transpose(vector2), what you end up with is the product of each possible pairing of values in vector1 and vector2.
2) You are looking for the maximum of those products.
3) I would hypothesize (you may need to do enough leg work to prove to yourself that this would be true) that the maximum of the products will be the product of the maxima.
4) If (3) is true, then you can find the maximum value by finding the two maxima =MAX(C2:C7) =MAX(D2:D7). The maximum value in the result matrix will simply be the product of these two values =PRODUCT(MAX(C2:C7),MAX(D2:D7))
5) Thinking through the way that matrix multiplication works on two vectors like this, the position of a value in vector1 corresponds to the row number in the result matrix, and the position of a value in vector2 corresponds to the column number in the result matrix. The MATCH() function is well suited to finding the position of a value within a vector (https://support.office.com/en-us/art...9-533f4a37673a ), so, the desired row number would =MATCH(MAX(C2:C7),C2:C7,0).

A lot of that development is math and not programming. But, if you can convince yourself that (3) is true, then the programming part is down to two MAX() functions, two MATCH() functions, and a PRODUCT() function, and you have successfully avoided computing the entire result matrix.

I'm not sure what result you want.

The max value will be obtained by multiplying the max values from each column.

So, max from C2:C7 = 0.5 = relative position = 5

Max from D2:D7 = 0.4 = relative position = 2.

Do you want these values returned to separate cells or combined into a single cell?

Originally Posted by kvsrinivasamurthy

ARRAY formulas
For ROW

=LARGE(IF(MMULT($C$2:$C$7,TRANSPOSE($D$2:$D$7))=MAX(MMULT($C$2:$C$7,TRANSPOSE($D$2:$D$7))),ROW($C$2:$C$7),""),1)-ROW($C$2)+1

For COLUMN

=LARGE(IF(MMULT($C$2:$C$7,TRANSPOSE($D$2:$D$7))=MAX(MMULT($C$2:$C$7,TRANSPOSE($D$2:$D$7))),TRANSPOSE(ROW($D$2:$D$7)-ROW($D$2)+1),""),1)

I think you're overcomplicating it.

Row:

=MATCH(MAX(C2:C7),C2:C7,0)

Column:

=MATCH(MAX(D2:D7),D2:D7,0)

What I didn't mention from the beginning, is that I'll eventually need the top n values in the matrix (and thus probably got focused in on the LARGE function), but suspect that I can find those values via a similar line of thinking.

Maybe, but I think this logic will become rather complex rather quickly as you work your way down through the n largest.

kvsrinivasamurthy's solution seems to work, and may be easily adapted to the n largest, but I note that it performs the matrix multiplication at least twice per function (and two functions per nth-largest value). Depending on how many values you end up looking for, it seems that there is getting to be a lot of extra computer and programming effort to avoid using a few cells. I will come back to my first question -- with 16 million cells per tab (and tabs limited only by available memory), why are we going to such lengths to avoid calculating and storing the result matrix in the spreadsheet?