# How to return a result's position in a 2D array?

#### jp1981

##### New Member
Hi

I have a list of 107 stocks and have generated a correlation matrix between them (i.e. I have a 107x107 matrix of results).

Next step I have used LARGE() to generate an ordered list of the highest results.

How can I map those results back to the names of the stocks?

i.e. generically, I have two 107x107 matrices, how can I take a result from one matrix and return the analogously-positioned result from the other matrix?

or another way of looking at it is how to return a result's grid ref {X,Y} from a matrix?

The only thing i've thought of is for each row or column take a slice of the 1st, 2nd, 3rd highest results but that relies on me not looking to return that many results as it isn't scalable (i'm probably looking at the top 400 results from this ~11000 result matrix).

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Gerald Higgins

##### Well-known Member
The MATCH function can be used to show an item's position in an array.

#### jp1981

##### New Member
only a one-dimensional array though, not a matrix

#### jp1981

##### New Member
I'm very familiar with the concept of using MATCH against row and column headings and then INDEX on these two results to get the correct entry in a grid of results.

This isn't that problem. Imagine a 9 entry matrix as follows

{1, 2, 3}
{4, 5, 6}
{7, 8, 9}

i.e. cell A1 contains 1, C3 contains 9.

I need a function ARRAYFIND() such that ARRAYFIND(6) = C2
or = {2,3} in coordinate form.

The numbers in the grid are non-rounded correlation coefficients so will all be unique.

Thanks

#### pgc01

##### MrExcel MVP
Hi

If I understand correctly you want a reverse lookup on a 2D matrix.

Try in H3:

=MIN(IF(\$B\$2:\$E\$6=H\$2,ROW(\$B\$2:\$E\$6)-ROW(\$B\$2)+1))

... confirm with CTRL-SHIFT-ENTER

In H4:

=IF(H3=0,0,MATCH(H\$2,INDEX(\$B\$2:\$E\$6,H\$3,0),0))

Copy down.

A result of 0 in the row and column means the value was not found.

#### mikerickson

##### MrExcel MVP
Try
=SUMPRODUCT(--(dataRange=12)*ROW(dataRange)) and
=SUMPRODUCT(--(dataRange=12)*COLUMN(dataRange))

These will return the Row and column number of the cell containing 12.
Note that the are Row and Column numbers (relative to the whole worksheet) not indices (relative to dataRange)

=SUMPRODUCT(--(dataRange=12)*ROW(dataRange)) - ROW(dataRange) +1
=SUMPRODUCT(--(dataRange=12)*COLUMN(dataRange)) - Column(dataRange) +1

will return indices.

• plasticrelic

#### jp1981

##### New Member
Thanks chaps. Two good results, neither of which I understand!

FYI Mike your solution is a bit more efficient in terms of sheet size so I think i'll go with that one

#### Sean 35813

##### New Member
Hi JP.

The following code should give you the ARRAYFIND function you asked for. It has two arguments. The first is the value you are looking for, the second is the range of the table. So, for instance, write "=ARRAYFIND(6,A1:C3)".

Code:
``````Public Function ARRAYFIND(target1 As Double, target2 As Range)

For Each cell In target2

If cell.Value = target1 Then
variable1 = cell.Row
variable2 = cell.Column
End If

Next

ARRAYFIND = variable3

End Function``````

#### mikerickson

##### MrExcel MVP
My solution is pretty simple: given dataRange in A2:C4 of

11 22 33
44 55 66
77 88 99

--(dataRange=66) returns the array
0 0 0
0 0 1
0 0 0

Since each of the data in the dataRange is unique, there will be only one 1 in that array.
The array ROW(dataRange) is
2 2 2
3 3 3
4 4 4

Multiplyting those two arrays component-wise gives the array
0 0 0
0 0 3
0 0 0

The Sum of which is 3, the row number (relative to the sheet) of the cell that contains 66.

Subtract ROW(dataRange) (i.e.2) and add 1 to get 3-2+1 = 2, the Row index of that cell relative to dataRange.

Last edited: