Referencing Items in Multi-dimensional Arrays

Posted by Mike Bell on April 26, 2001 4:36 AM

have two arrays (named Array1 and Array2, funnily enough!) that are both of equal dimensions.

I am looking for a formula that will return the position of the maximum value in Array1 and then return the value in the corresponding position from Array2.

For example, if the max value in Array1 is in position [4,3], then I need to return the value in position [4,3] from Array2.

I am ripping my hair out to do this using excel formulas. I have developed a very long way round but I don't like going from A to B through C, D and E!!!!

Please help.

Posted by Aladin Akyurek on April 26, 2001 4:45 AM

Posted by Aladin Akyurek on April 26, 2001 4:49 AM

Select all the cells of Array1 and Array2 and name the selection DATA via the Name Box. Select the cells of Array1 and name it (if not done so yet) LVALUES.

Enter in some cell: =VLOOKUP(MAX(LVALUES),DATA,2,0)

Aladin

Posted by Mike Bell on April 26, 2001 4:52 AM

Thanks for the help Aladin - Nice one :-)

Thanks

Posted by Mike Bell on April 26, 2001 5:05 AM

FAO Aladin

Thanks for the help.

Tried what you suggested but returned #NA error.

Could I send you the workbook with the example in please (or you send me an example)?

sorry to be a pain.

Thanks

Mike

Posted by Aladin Akyurek on April 26, 2001 6:15 AM

Re: FAO Aladin

Mike

#N/A means that the lookup value (in your case MAX(LVALUES)) is not available in DATA, the lookup table. In your case, it's strange, because the MAX value has been computed from the 1st column of DATA, thus it's there.

An example is underway to you.

Aladin

Posted by Aladin Akyurek on April 26, 2001 9:57 AM

Solution...

Mike

It's obvious (after seeing your example data) that I misread the problem. A sin too many, which dragged me into this difficult case. Sigh. Here we go, but first your example data.

{3,4,5,3,4,5;7,8,9,2,3,4;10,11,12,"mike","aladin","mike"}

Lets say that these data occupies the range A3:F5.

You want to compute the MAX of values occupying A3:C5 (matrix1) and retrieve the value of the cell that occupies the same position in the range D3:F5 (matrix2).

In this specific example, the max value in matrix1 is 12 and its position in this matrix is (3,3). The value that occupies the (3,3) position in matrix2 is 'mike'.

In G3 enter: =ADDRESS(ROW(F3),COLUMN(F3)) [ copy down this to G5. ]
In H3 enter: =ROW() [ copy down this to H5. ]
in I3 array-enter: =IF(ISNUMBER(MATCH(MAX($A$3:$C$5), TRANSPOSE($A3:$C3),0)),MATCH(MAX($A$3:$C$5), TRANSPOSE($A3:$C3),0),"") [ You need to hit CONTROL+SHIFT+ENTER to enter this formula; Then copy down this to I5. ]
In J3 enter: =IF(ISNUMBER(I3), VLOOKUP(MAX($A$3:$C$5), INDIRECT(ADDRESS(H3,I3)&":"&G3),I3+1,0),"") [ copy down this to J5. ]

The above system of formulas will produce somewhere in column J the value you are looking for.

I hope someone has something better/shorter on offer.

Aladin