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

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

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).

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

The MATCH function can be used to show an item's position in an array.

3. ## Re: How to return a result's position in a 2D array?

only a one-dimensional array though, not a matrix

4. ## Re: How to return a result's position in a 2D array?

Perhaps a combination of INDEX and MATCH like this http://www.j-walk.com/ss/excel/usertips/tip020.htm

5. ## Re: How to return a result's position in a 2D array?

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

6. ## Re: How to return a result's position in a 2D array?

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.

ABCDEFGHIJK
1
2 39711052870 Value29374
3 89626210137 Row405
4 34544424395 Column204
5 44129557663
6 5266027074
7
[Book1]Sheet1

7. ## Re: How to return a result's position in a 2D array?

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.

8. ## Re: How to return a result's position in a 2D array?

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

9. ## Re: How to return a result's position in a 2D array?

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

10. ## Re: How to return a result's position in a 2D array?

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•