CODEWITHSUNDEEP
excelindexingmatchvlookup
bart1701
bart1701

Reputation: 65

Index Match in Matrix (multiple rows, mutiple cols)

I have a correlation sheet, where the output should be a list of top-5 correlations.

Problem: I can use the 'large' function to get the top five, but with no 'name tags'

I can use Index / Match to get the largest result, but only for one column..

i found multiple pages about this subject, but always in the opposite direction: http://www.atlaspm.com/toms-tutorials-for-excel/toms-tips-for-excel-look-up-intersecting-value-by-row-and-column-criteria/

there the values in ColumnA and Row1 are known, and a value matching them is missing, I need the names from ColumnA and Row1 given that I know the outcome

to make it more clear:

enter image description here

enter image description here

so next to the column where the top 5 is shown (1, 0.99, 0.97 ect.) there should be: AA, CA, EC, ED, BA

Any thoughts? Thanks

Upvotes: 1

Views: 173

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use this formula that finds the first row or column that matches:

=INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/($B$2:$F$6=B8),1))&INDEX($B$1:$F$1,AGGREGATE(15,6,(COLUMN($B$1:$F$1)-COLUMN($B$1)+1)/($B$2:$F$6=B8),1))

This concatenated two formulas. The first finds the row and the second the column.

enter image description here

One note: This only works if the values are unique. If there are duplicates it will cause false positives.

Upvotes: 2

Related Questions