Find 2 corresponding symbols from largest value in Matrix

[Dustinkli]

I have a matrix, pictured below and I want a formula that can find the top 5 or so largest values in the matrix and the corresponding stock symbols associated with that largest number.

So for instance if the top # is 90% then it would show "FANG" and "XOP". The top # 2 is 86% it would show AMD and SMH.

I've tried numerous things with no success. Does anyone have any recommendations? In my spreadsheet the above matrix is in cells C121:W142.

 

[Peter_SSs]

There is a lot of typing to set something like that up to test. Could we have it with XL2BB so that we can easily copy for testing?

 

[Dustinkli]

I was having trouble getting XL2BB to work but here are the cells you should be able to copy/paste:

	MSFT	SMH	INTC	SBNY	LNG	ADBE	HOOD	XOP	AMD	META	OLPX	ITB	HDSN	SOFI	UPST	CLR	FANG	ET	PKG	MMM	SPY
MSFT	100.00%	75.80%	55.25%	46.55%	18.85%	76.51%	29.36%	21.56%	63.97%	58.07%	48.39%	57.44%	25.07%	38.33%	35.16%	16.52%	15.93%	23.55%	36.02%	34.53%	84.62%
SMH	75.80%	100.00%	73.07%	57.85%	26.61%	69.95%	44.14%	34.25%	85.79%	63.63%	58.01%	64.41%	32.00%	49.61%	45.18%	22.13%	26.99%	29.84%	32.32%	39.74%	85.15%
INTC	55.25%	73.07%	100.00%	45.73%	28.46%	47.60%	32.81%	32.87%	58.81%	50.05%	37.43%	48.42%	18.47%	37.65%	35.23%	23.17%	26.95%	32.03%	26.87%	39.76%	67.00%
SBNY	46.55%	57.85%	45.73%	100.00%	26.39%	38.09%	35.53%	40.90%	49.58%	43.97%	42.88%	55.33%	27.33%	38.26%	41.00%	37.29%	39.39%	43.21%	43.67%	39.47%	66.23%
LNG	18.85%	26.61%	28.46%	26.39%	100.00%	13.44%	9.49%	63.68%	25.66%	18.23%	20.00%	17.55%	9.47%	16.19%	19.39%	55.35%	56.81%	49.76%	16.63%	14.00%	34.22%
ADBE	76.51%	69.95%	47.60%	38.09%	13.44%	100.00%	36.62%	17.88%	60.31%	54.10%	53.18%	55.08%	19.26%	39.11%	36.01%	11.16%	9.45%	14.00%	22.23%	30.86%	71.65%
HOOD	29.36%	44.14%	32.81%	35.53%	9.49%	36.62%	100.00%	13.49%	47.24%	35.59%	41.10%	32.28%	15.59%	52.98%	40.91%	7.14%	9.63%	14.31%	11.67%	15.34%	38.20%
XOP	21.56%	34.25%	32.87%	40.90%	63.68%	17.88%	13.49%	100.00%	29.95%	21.67%	27.32%	25.14%	17.39%	20.93%	20.77%	83.57%	89.62%	70.60%	25.47%	16.41%	43.73%
AMD	63.97%	85.79%	58.81%	49.58%	25.66%	60.31%	47.24%	29.95%	100.00%	52.52%	53.99%	52.89%	29.11%	46.76%	43.30%	18.24%	22.99%	26.14%	23.56%	30.60%	71.60%
META	58.07%	63.63%	50.05%	43.97%	18.23%	54.10%	35.59%	21.67%	52.52%	100.00%	49.46%	48.30%	22.45%	44.39%	40.55%	14.23%	14.85%	15.99%	26.49%	28.63%	64.27%
OLPX	48.39%	58.01%	37.43%	42.88%	20.00%	53.18%	41.10%	27.32%	53.99%	49.46%	100.00%	55.65%	29.98%	41.04%	45.75%	25.22%	21.69%	17.22%	30.89%	27.69%	58.82%
ITB	57.44%	64.41%	48.42%	55.33%	17.55%	55.08%	32.28%	25.14%	52.89%	48.30%	55.65%	100.00%	21.93%	38.36%	42.87%	19.19%	21.20%	27.83%	48.76%	52.97%	74.07%
HDSN	25.07%	32.00%	18.47%	27.33%	9.47%	19.26%	15.59%	17.39%	29.11%	22.45%	29.98%	21.93%	100.00%	20.60%	23.74%	14.53%	12.09%	11.12%	21.71%	12.01%	30.78%
SOFI	38.33%	49.61%	37.65%	38.26%	16.19%	39.11%	52.98%	20.93%	46.76%	44.39%	41.04%	38.36%	20.60%	100.00%	54.24%	15.96%	15.56%	24.68%	12.91%	20.65%	45.17%
UPST	35.16%	45.18%	35.23%	41.00%	19.39%	36.01%	40.91%	20.77%	43.30%	40.55%	45.75%	42.87%	23.74%	54.24%	100.00%	13.23%	17.11%	18.58%	14.92%	24.33%	45.10%
CLR	16.52%	22.13%	23.17%	37.29%	55.35%	11.16%	7.14%	83.57%	18.24%	14.23%	25.22%	19.19%	14.53%	15.96%	13.23%	100.00%	79.96%	58.00%	22.38%	14.01%	33.70%
FANG	15.93%	26.99%	26.95%	39.39%	56.81%	9.45%	9.63%	89.62%	22.99%	14.85%	21.69%	21.20%	12.09%	15.56%	17.11%	79.96%	100.00%	61.27%	21.39%	16.50%	36.97%
ET	23.55%	29.84%	32.03%	43.21%	49.76%	14.00%	14.31%	70.60%	26.14%	15.99%	17.22%	27.83%	11.12%	24.68%	18.58%	58.00%	61.27%	100.00%	24.37%	23.49%	42.97%
PKG	36.02%	32.32%	26.87%	43.67%	16.63%	22.23%	11.67%	25.47%	23.56%	26.49%	30.89%	48.76%	21.71%	12.91%	14.92%	22.38%	21.39%	24.37%	100.00%	42.84%	52.69%
MMM	34.53%	39.74%	39.76%	39.47%	14.00%	30.86%	15.34%	16.41%	30.60%	28.63%	27.69%	52.97%	12.01%	20.65%	24.33%	14.01%	16.50%	23.49%	42.84%	100.00%	55.63%
SPY	84.62%	85.15%	67.00%	66.23%	34.22%	71.65%	38.20%	43.73%	71.60%	64.27%	58.82%	74.07%	30.78%	45.17%	45.10%	33.70%	36.97%	42.97%	52.69%	55.63%	100.00%

 

[Peter_SSs]

I was having trouble getting XL2BB to work

Can you describe at what point of the instructions that it went wrong and what it was that went wrong? We might be able to assist for next time.

See if this would work for you.
Note that I have changed some of the sample data so that there some tied values. The colouring was just for my own benefit in getting/checking the results.

Dustinkli.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
120		MSFT	SMH	INTC	SBNY	LNG	ADBE	HOOD	XOP	AMD	META	OLPX	ITB	HDSN	SOFI	UPST	CLR	FANG	ET	PKG	MMM	SPY
121	MSFT	100.00%	75.80%	55.25%	89.62%	18.85%	76.51%	29.36%	21.56%	63.97%	58.07%	48.39%	57.44%	25.07%	38.33%	35.16%	16.52%	15.93%	23.55%	36.02%	34.53%	84.62%
122	SMH	75.80%	100.00%	73.07%	57.85%	26.61%	69.95%	44.14%	34.25%	85.79%	63.63%	58.01%	64.41%	32.00%	49.61%	45.18%	22.13%	26.99%	29.84%	32.32%	39.74%	85.15%
123	INTC	55.25%	73.07%	100.00%	45.73%	28.46%	47.60%	32.81%	32.87%	58.81%	50.05%	37.43%	48.42%	18.47%	37.65%	35.23%	23.17%	26.95%	32.03%	26.87%	39.76%	67.00%
124	SBNY	89.62%	57.85%	45.73%	100.00%	26.39%	38.09%	35.53%	40.90%	49.58%	43.97%	42.88%	55.33%	27.33%	38.26%	41.00%	37.29%	39.39%	43.21%	43.67%	39.47%	66.23%
125	LNG	18.85%	26.61%	28.46%	26.39%	100.00%	13.44%	9.49%	63.68%	25.66%	18.23%	20.00%	17.55%	9.47%	16.19%	19.39%	55.35%	56.81%	49.76%	16.63%	14.00%	34.22%
126	ADBE	76.51%	69.95%	47.60%	38.09%	13.44%	100.00%	36.62%	17.88%	60.31%	54.10%	53.18%	55.08%	19.26%	39.11%	36.01%	11.16%	9.45%	14.00%	22.23%	30.86%	71.65%
127	HOOD	29.36%	44.14%	32.81%	35.53%	9.49%	36.62%	100.00%	13.49%	47.24%	35.59%	41.10%	32.28%	15.59%	52.98%	40.91%	7.14%	9.63%	14.31%	11.67%	15.34%	38.20%
128	XOP	21.56%	34.25%	32.87%	40.90%	63.68%	17.88%	13.49%	100.00%	29.95%	21.67%	27.32%	25.14%	17.39%	20.93%	20.77%	83.57%	89.62%	70.60%	89.62%	16.41%	43.73%
129	AMD	63.97%	85.79%	58.81%	49.58%	25.66%	60.31%	47.24%	29.95%	100.00%	52.52%	53.99%	52.89%	29.11%	46.76%	43.30%	18.24%	22.99%	26.14%	23.56%	30.60%	71.60%
130	META	58.07%	63.63%	50.05%	43.97%	18.23%	54.10%	35.59%	21.67%	52.52%	100.00%	49.46%	48.30%	22.45%	44.39%	40.55%	14.23%	14.85%	15.99%	26.49%	28.63%	64.27%
131	OLPX	48.39%	58.01%	37.43%	42.88%	20.00%	53.18%	41.10%	27.32%	53.99%	49.46%	100.00%	55.65%	29.98%	41.04%	45.75%	25.22%	21.69%	17.22%	30.89%	27.69%	58.82%
132	ITB	57.44%	64.41%	48.42%	55.33%	17.55%	55.08%	32.28%	25.14%	52.89%	48.30%	55.65%	100.00%	21.93%	38.36%	42.87%	19.19%	21.20%	27.83%	48.76%	52.97%	74.07%
133	HDSN	25.07%	32.00%	18.47%	27.33%	9.47%	19.26%	15.59%	17.39%	29.11%	22.45%	29.98%	21.93%	100.00%	20.60%	23.74%	14.53%	12.09%	11.12%	21.71%	12.01%	30.78%
134	SOFI	38.33%	49.61%	37.65%	38.26%	16.19%	39.11%	52.98%	20.93%	46.76%	44.39%	41.04%	38.36%	20.60%	100.00%	54.24%	15.96%	15.56%	24.68%	12.91%	20.65%	45.17%
135	UPST	35.16%	45.18%	35.23%	41.00%	19.39%	36.01%	40.91%	20.77%	43.30%	40.55%	45.75%	42.87%	23.74%	54.24%	100.00%	13.23%	17.11%	18.58%	14.92%	24.33%	45.10%
136	CLR	16.52%	22.13%	23.17%	37.29%	55.35%	11.16%	7.14%	83.57%	18.24%	14.23%	25.22%	19.19%	14.53%	15.96%	13.23%	100.00%	79.96%	58.00%	22.38%	14.01%	33.70%
137	FANG	15.93%	26.99%	26.95%	39.39%	56.81%	9.45%	9.63%	89.62%	22.99%	14.85%	21.69%	21.20%	12.09%	15.56%	17.11%	79.96%	100.00%	61.27%	21.39%	16.50%	36.97%
138	ET	23.55%	29.84%	32.03%	43.21%	49.76%	14.00%	14.31%	70.60%	26.14%	15.99%	17.22%	27.83%	11.12%	24.68%	18.58%	58.00%	61.27%	100.00%	24.37%	23.49%	42.97%
139	PKG	36.02%	32.32%	26.87%	43.67%	16.63%	22.23%	11.67%	89.62%	23.56%	26.49%	30.89%	48.76%	21.71%	12.91%	14.92%	22.38%	21.39%	24.37%	100.00%	42.84%	52.69%
140	MMM	34.53%	39.74%	39.76%	39.47%	14.00%	30.86%	15.34%	16.41%	30.60%	28.63%	27.69%	52.97%	12.01%	20.65%	24.33%	14.01%	16.50%	23.49%	42.84%	100.00%	55.63%
141	SPY	84.62%	85.15%	67.00%	66.23%	34.22%	71.65%	38.20%	43.73%	71.60%	64.27%	58.82%	74.07%	30.78%	45.17%	45.10%	33.70%	36.97%	42.97%	52.69%	55.63%	100.00%
142
143
144		89.62%	SBNY	MSFT
145		89.62%	FANG	XOP
146		89.62%	PKG	XOP
147		85.79%	AMD	SMH
148		85.15%	SPY	SMH
Sheet3

Cell Formulas
Range	Formula
C144:C148	C144	=TRANSPOSE(AGGREGATE(14,6,C121:W141/(C121:W141<1),{1,3,5,7,9}))
D144:D148	D144	=INDEX(B:B,AGGREGATE(15,6,ROW(B$121:B$141)/((C$121:W$141=C144)*((COLUMN(C$121:W$141)-COLUMN($C121))<(ROW(C$121:W$141)-ROW(C$121)))),COUNTIF(C$144:C144,C144)))
E144:E148	E144	=INDEX(B:B,AGGREGATE(15,6,ROW(B$121:B$141)/((C$121:W$141=C144)*((COLUMN(C$121:W$141)-COLUMN($C121))>(ROW(C$121:W$141)-ROW(C$121)))),COUNTIF(C$144:C144,C144)))
Dynamic array formulas.