Lookup value in a Matrix based on 1 reference value

[Dustinkli]

Hello,

I have a matrix as shown below:

	Asset A	Asset B	Asset C	Asset D
Asset A	100%	75%	55%	32%
Asset B	75%	100%	25%	87%
Asset C	55%	25%	100%	87%
Asset D	32%	87%	90%	100%

I would like to create a formula that shows the most correlated assets in the matrix. I'm not sure how to do this. I figured that perhaps using the "LARGE" function and excluding the two assets that are the same (Thus 100%) and making a column of the top x # of correlation #s and then trying to do an index or match with Vlookup based on that but everything I try fails. I know how to do a lookup if it were just 2 columns but in a matrix it is throwing me off. Maybe I'm missing something.

 

[DanteAmor]

It would be great if you put the result you want.
I will venture to see if this is what you need:

Dante Amor
	A	B	C	D	E	F	G	H	I
1		Asset A	Asset B	Asset C	Asset D		Asset 1	Asset 2	%
2	Asset A	100%	75%	55%	32%		Asset D	Asset C	90%
3	Asset B	75%	100%	25%	87%		Asset D	Asset B	87%
4	Asset C	55%	25%	100%	87%		Asset C	Asset D	87%
5	Asset D	32%	87%	90%	100%		Asset B	Asset D	87%
6							Asset B	Asset A	75%
7							Asset A	Asset B	75%
Hoja10

Cell Formulas
Range	Formula
G2:G7	G2	=INDEX($A$2:$A$5,LARGE(IF(I2=$B$2:$E$5,ROW($A$2:$A$5)),COUNTIF($I$1:I2,I2))-ROW($A$2)+1)
H2:H7	H2	=INDEX($B$1:$E$1,,SMALL(IF(I2=$B$2:$E$5,COLUMN($B$1:$E$1)),COUNTIF($I$1:I2,I2))-COLUMN($B$1)+1)
I2:I7	I2	=LARGE(IF($B$2:$E$5<1,$B$2:$E$5),ROWS(J$2:J2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dustinkli]

That looks like it might work, but when I paste it into my excel I get #VALUE! errors for everything.

 

[Eric W]

If you have Excel 365 with the new SEQUENCE function, then this should work::

Book1
	A	B	C	D	E	F	G	H	I
1		Asset A	Asset B	Asset C	Asset D		Asset 1	Asset 2	%
2	Asset A	100%	75%	55%	32%		Asset D	Asset C	90%
3	Asset B	75%	100%	25%	87%		Asset D	Asset B	87%
4	Asset C	55%	25%	100%	90%		Asset B	Asset A	75%
5	Asset D	32%	87%	90%	100%		Asset C	Asset A	55%
6							Asset D	Asset A	32%
7							Asset C	Asset B	25%
8
Sheet8

Cell Formulas
Range	Formula
G2:I7	G2	=SORT(FILTER(CHOOSE({1,2,3},INDEX(A2:A5,SEQUENCE(COUNTA(B2:E5),,,1/COLUMNS(B2:E5))),INDEX(B1:E1,MOD(SEQUENCE(COUNTA(B2:E5),,0),COLUMNS(B2:E5))+1),INDEX(B2:E5,SEQUENCE(COUNTA(B2:E5),,,1/COLUMNS(B2:E5)),MOD(SEQUENCE(COUNTA(B2:E5),,0),COLUMNS(B2:E5))+1)),INT(SEQUENCE(COUNTA(B2:E5),,,1/COLUMNS(B2:E5)))>MOD(SEQUENCE(COUNTA(B2:E5),,0),COLUMNS(B2:E5))+1),3,-1)
Dynamic array formulas.

If you have the new LET function as well, that formula can be shortened quite a bit.

 

[DanteAmor]

That looks like it might work, but when I paste it into my excel I get #VALUE! errors for everything.

All formulas are array formulas, to accept you must press Control + Shift + Enter, not just Enter.