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

[jp1981]

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

Please help...

 

[platonic567]

My solution is pretty simple: given dataRange in A2:C4

The array ROW(dataRange) is

2 2 2
3 3 3
4 4 4

+1

Nice formula with Sumproduct.

Not sure if I am missing something here, but the formula =ROW(A2:C4) in excel will give an array of

2
3
4

In order to generate:


2 2 2
3 3 3
4 4 4

I had to slightly modify the formula to =ROW(A2:C4)*{1,1,1}

FYI, for anyone still reading this old thread.

 

[mikerickson]

The --(dataRange=12) part is a two-D array that, by multiplication, will spread the (1-D) ROW(DataRange) out to two dimensions.

 

[platonic567]

Thanks Mike, I completely missed the asterisk symbol and ended up separating the ROW() part into another array.

 

[tybaltlives]

hey guys, I know this thread is ancient, but I wanted to pass along that all the solutions are very clever and useful. The formula versions, at least, work with strings as well. Great way to find indices in 2x text table. So thanks.

 

[sapid]

I am trying to make this wonderful solution using SUMPRODUCT work with Structured References with Tables so I can add a column to the table with results from the SUMPRODUCT. Following are my efforts. On the left are the answers I get. On the right are the formulas used. $Q$2 is one of the table entries. In the table to be modified, it becomes [@Subj]. All table elements are unique. I don't know why I get different answers with structured referencing and traditional ranges, as shown below. I am new to Tables so may be missing a subtlety.

[TABLE="width: 325"]
<tbody>[TR]
[TD="colspan: 3"]How many there are of $Q$2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="colspan: 4"]=SUMPRODUCT(--(tblSubjectsByCollege[#Data]=$Q$2))[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 4"]=SUMPRODUCT(--($G$2:$O$51=$Q$2))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Find first column of the data range[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="colspan: 3"]=COLUMN($G$2:$O$51)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="colspan: 3"]=COLUMN(tblSubjectsByCollege)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="colspan: 4"]=COLUMN(tblSubjectsByCollege[#Data])[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Find the column of the found cell[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="colspan: 4"]=SUMPRODUCT(--(tblSubjectsByCollege[#Data]=$Q$2))*COLUMN($G$2:$O$51)[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="colspan: 4"]=SUMPRODUCT(--(tblSubjectsByCollege[#Data]=$Q$2))*COLUMN(tblSubjectsByCollege)[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="colspan: 4"]=SUMPRODUCT(--($G$2:$O$51=$Q$2)*COLUMN($G$2:$O$51))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="colspan: 4"]=SUMPRODUCT(--(tblSubjectsByCollege[#Data]=$Q$2))*COLUMN(tblSubjectsByCollege)-COLUMN(tblSubjectsByCollege)+1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="colspan: 4"]=SUMPRODUCT(--($G$2:$O$51=$Q$2)*COLUMN($G$2:$O$51))-COLUMN($G$2:$O$51)+1[/TD]
[/TR]
</tbody>[/TABLE]

 

[sapid]

I found the answer, thanks to Charley Kyd:

How to Use SUMPRODUCT to Create Two-Dimensional Lookups in Excel Formulas

Here are his formulas applied to my table which contains unique entries:

[TABLE="width: 946"]
<tbody>[TR]
[TD="colspan: 3"][TABLE="width: 946"]
<tbody>[TR]
[TD][/TD]
[TD]Results[/TD]
[TD]Find Subject, where Subject is a named cell containing the search term.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row #[/TD]
[TD]16[/TD]
[TD]=SUMPRODUCT(ROW(tblSubjectsByCollege[#Data])*(tblSubjectsByCollege[#Data]=Subject))-ROW(tblSubjectsByCollege[#Data])+1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column #[/TD]
[TD]9[/TD]
[TD]=SUMPRODUCT(COLUMN(tblSubjectsByCollege[#Data])*(tblSubjectsByCollege[#Data]=Subject))-COLUMN(tblSubjectsByCollege[#Data])+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The row and column numbers are with respect to tblSubjectsByCollege[#Data], not the worksheet.

Note that instead of using SUMPRODUCT(--Data=FindVal), he uses SUMPRODUCT((Data=FindVal)*1) to turn TRUE/FALSE into 1/0. For me, the result is much easier to understand.

 

[wiz329]

How would the formula differ if the entries are not unique? Essentially, I'm dealing with a dataset with a look-up table that I've brought in (bad practice, I know). So the row values are *not* unique, but each value will be in one and only one column. I'm trying to find that column index. But I don't know which row it might be in.

 

[mikerickson]

wiz329
I'm not sure what your situation is.

You have a 2-D range of cells, there is only one column that has "soughtValue" in it, but there might be many rows in that column that contain "soughtValue".

Is that your situation?

 

[wiz329]

wiz329
I'm not sure what your situation is.

You have a 2-D range of cells, there is only one column that has "soughtValue" in it, but there might be many rows in that column that contain "soughtValue".

Is that your situation?

Correct.

 

[mikerickson]

To get the column number, try
=SUMPRODUCT(--(dataRange="soughtValue")*COLUMN(dataRange))/COUNTIF(dataRange, "soughtValue")